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.identitystore.business.identity;
35
36 import com.fasterxml.jackson.core.JsonProcessingException;
37 import com.fasterxml.jackson.core.type.TypeReference;
38 import com.fasterxml.jackson.databind.ObjectMapper;
39 import fr.paris.lutece.plugins.identitystore.business.attribute.AttributeCertificate;
40 import fr.paris.lutece.plugins.identitystore.business.attribute.AttributeKey;
41 import fr.paris.lutece.plugins.identitystore.business.attribute.KeyType;
42 import fr.paris.lutece.plugins.identitystore.v3.web.rs.dto.common.AuthorType;
43 import fr.paris.lutece.plugins.identitystore.v3.web.rs.dto.history.AttributeChange;
44 import fr.paris.lutece.plugins.identitystore.v3.web.rs.dto.history.AttributeChangeType;
45 import fr.paris.lutece.plugins.identitystore.web.exception.IdentityStoreException;
46 import fr.paris.lutece.portal.service.plugin.Plugin;
47 import fr.paris.lutece.util.sql.DAOUtil;
48 import org.apache.commons.lang3.StringUtils;
49
50 import java.sql.Statement;
51 import java.util.ArrayList;
52 import java.util.Date;
53 import java.util.LinkedHashMap;
54 import java.util.List;
55 import java.util.Map;
56 import java.util.stream.Collectors;
57
58
59
60
61 public final class IdentityAttributeDAO implements IIdentityAttributeDAO
62 {
63
64 private static final String SQL_QUERY_INSERT = "INSERT INTO identitystore_identity_attribute ( id_identity, id_attribute, attribute_value, id_certification, id_file, lastupdate_client ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
65 private static final String SQL_QUERY_DELETE = "DELETE FROM identitystore_identity_attribute WHERE id_identity = ? AND id_attribute = ?";
66 private static final String SQL_QUERY_DELETE_ALL_ATTR = "DELETE FROM identitystore_identity_attribute WHERE id_identity = ?";
67 private static final String SQL_QUERY_UPDATE = "UPDATE identitystore_identity_attribute SET id_identity = ?, id_attribute = ?, attribute_value = ?, id_certification = ?, id_file = ?, lastupdate_date = CURRENT_TIMESTAMP, lastupdate_client = ? WHERE id_identity = ? AND id_attribute = ? ";
68 private static final String SQL_COMMON_SELECT = "SELECT a.id_attribute as id_attribute, a.id_identity as id_identity, a.attribute_value as attribute_value, a.id_certification as id_certification, a.lastupdate_date as lastupdate_date, a.lastupdate_client as lastupdate_client,"
69 + " c.certifier_code as certifier_code, c.certificate_date as certificate_date, c.expiration_date as expiration_date, "
70 + " r.id_attribute as attribute_key_id, r.key_name as attribute_key_name, r.name as attribute_name, r.description as attribute_description, r.certifiable as certifiable, r.common_search_key as common_search_key, "
71 + " r.key_type as attribute_key_type, r.key_weight as attribute_key_weight, r.mandatory_for_creation as mandatory_for_creation, r.pivot as pivot, r.validation_error_message as validation_error_message, r.validation_regex as validation_regex "
72 + "FROM identitystore_identity_attribute a "
73 + " LEFT JOIN identitystore_identity_attribute_certificate c ON c.id_attribute_certificate = a.id_certification "
74 + " LEFT JOIN identitystore_ref_attribute r ON r.id_attribute = a.id_attribute ";
75 private static final String SQL_QUERY_SELECTALL = SQL_COMMON_SELECT + " WHERE a.id_identity = ? ORDER BY a.id_attribute";
76 private static final String SQL_QUERY_SELECT_BY_LIST_IDENTITY = SQL_COMMON_SELECT + " WHERE a.id_identity IN (${list_identity})";
77
78
79 private static final String SQL_QUERY_INSERT_HISTORY = "INSERT INTO identitystore_identity_attribute_history "
80 + " (change_type, change_satus, change_message, author_type, author_name, client_code, id_identity, attribute_key, attribute_value, certification_process, certification_date, modification_date, metadata) "
81 + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, to_json(?::json))";
82 private static final String SQL_QUERY_SELECT_ATTRIBUTE_HISTORY = "SELECT id_history, change_type, change_satus, change_message, author_type, author_name, client_code, id_identity, attribute_key, attribute_value, certification_process, certification_date, modification_date, metadata::text FROM identitystore_identity_attribute_history WHERE id_identity = ? ORDER BY modification_date DESC";
83 private static final String SQL_QUERY_SELECT_ATTRIBUTE_HISTORY_BY_CUSTOMER_ID = "SELECT a.id_history, a.change_type, a.change_satus, a.change_message, a.author_type, a.author_name, a.client_code, a.id_identity, a.attribute_key, a.attribute_value, a.certification_process, a.certification_date, a.modification_date, a.metadata::text FROM identitystore_identity_attribute_history a JOIN identitystore_identity i ON a.id_identity = i.id_identity WHERE i.customer_id = ? ORDER BY a.modification_date DESC";
84 private static final String SQL_QUERY_DELETE_ALL_HISTORY = "DELETE FROM identitystore_identity_attribute_history WHERE id_identity = ?";
85
86 private final ObjectMapper objectMapper = new ObjectMapper( );
87
88
89
90
91 @Override
92 public void insert( IdentityAttribute identityAttribute, Plugin plugin )
93 {
94 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin ) )
95 {
96 int nIndex = 1;
97
98 daoUtil.setInt( nIndex++, identityAttribute.getIdIdentity( ) );
99 daoUtil.setInt( nIndex++, identityAttribute.getAttributeKey( ).getId( ) );
100 daoUtil.setString( nIndex++, identityAttribute.getValue( ) );
101 daoUtil.setInt( nIndex++, identityAttribute.getIdCertificate( ) );
102 daoUtil.setInt( nIndex++, ( identityAttribute.getFile( ) != null ) ? identityAttribute.getFile( ).getIdFile( ) : 0 );
103 daoUtil.setString( nIndex, identityAttribute.getLastUpdateClientCode( ) );
104
105 daoUtil.executeUpdate( );
106 }
107 }
108
109
110
111
112 @Override
113 public void delete( int nIdentityId, int nAttributeId, Plugin plugin )
114 {
115
116
117 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin ) )
118 {
119 daoUtil.setInt( 1, nIdentityId );
120 daoUtil.setInt( 2, nAttributeId );
121 daoUtil.executeUpdate( );
122 }
123 }
124
125
126
127
128 @Override
129 public void store( IdentityAttribute identityAttribute, Plugin plugin )
130 {
131 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin ) )
132 {
133 int nIndex = 1;
134
135 daoUtil.setInt( nIndex++, identityAttribute.getIdIdentity( ) );
136 daoUtil.setInt( nIndex++, identityAttribute.getAttributeKey( ).getId( ) );
137 daoUtil.setString( nIndex++, identityAttribute.getValue( ) );
138 daoUtil.setInt( nIndex++, identityAttribute.getIdCertificate( ) );
139 daoUtil.setInt( nIndex++, ( identityAttribute.getFile( ) != null ) ? identityAttribute.getFile( ).getIdFile( ) : 0 );
140 daoUtil.setString( nIndex++, identityAttribute.getLastUpdateClientCode( ) );
141 daoUtil.setInt( nIndex++, identityAttribute.getIdIdentity( ) );
142 daoUtil.setInt( nIndex, identityAttribute.getAttributeKey( ).getId( ) );
143
144 daoUtil.executeUpdate( );
145 }
146 }
147
148
149
150
151 @Override
152 public Map<String, IdentityAttribute> selectAttributes( int nIdentityId, Plugin plugin )
153 {
154 final Map<String, IdentityAttribute> attributesMap = new LinkedHashMap<>( );
155 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin ) )
156 {
157 daoUtil.setInt( 1, nIdentityId );
158 daoUtil.executeQuery( );
159
160 while ( daoUtil.next( ) )
161 {
162 final IdentityAttribute identityAttribute = this.getIdentityAttribute( daoUtil );
163 attributesMap.put( identityAttribute.getAttributeKey( ).getKeyName( ), identityAttribute );
164 }
165
166 return attributesMap;
167 }
168 }
169
170
171
172
173 @Override
174 public List<IdentityAttribute> selectAllAttributesByIdentityList( List<Identity> listIdentity, Plugin plugin )
175 {
176 final List<IdentityAttribute> listIdentityAttributes = new ArrayList<>( );
177
178 if ( listIdentity == null || listIdentity.isEmpty( ) )
179 {
180 return listIdentityAttributes;
181 }
182
183 final String strSQL = SQL_QUERY_SELECT_BY_LIST_IDENTITY.replace( "${list_identity}",
184 listIdentity.stream( ).map( i -> String.valueOf( i.getId( ) ) ).collect( Collectors.joining( "," ) ) );
185
186 try ( final DAOUtil daoUtil = new DAOUtil( strSQL, plugin ) )
187 {
188 daoUtil.executeQuery( );
189
190 while ( daoUtil.next( ) )
191 {
192 listIdentityAttributes.add( this.getIdentityAttribute( daoUtil ) );
193 }
194
195 return listIdentityAttributes;
196 }
197 }
198
199
200
201
202
203
204
205 private boolean isCertificateExpired( IdentityAttribute attribute )
206 {
207 return attribute.getCertificate( ) != null && attribute.getCertificate( ).getExpirationDate( ) != null
208 && attribute.getCertificate( ).getExpirationDate( ).before( new Date( ) );
209 }
210
211 @Override
212 public void deleteAllAttributes( int nIdentityId, Plugin plugin )
213 {
214 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_HISTORY, plugin ) )
215 {
216 daoUtil.setInt( 1, nIdentityId );
217 daoUtil.executeUpdate( );
218 }
219
220 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_ALL_ATTR, plugin ) )
221 {
222 daoUtil.setInt( 1, nIdentityId );
223 daoUtil.executeUpdate( );
224 }
225 }
226
227 @Override
228 public synchronized void addAttributeChangeHistory( AttributeChange attributeChange, Plugin plugin ) throws IdentityStoreException
229 {
230 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_HISTORY, Statement.RETURN_GENERATED_KEYS, plugin ) )
231 {
232 int nIndex = 1;
233
234 daoUtil.setInt( nIndex++, attributeChange.getChangeType( ).getValue( ) );
235 daoUtil.setString( nIndex++, attributeChange.getChangeSatus( ) );
236 daoUtil.setString( nIndex++, attributeChange.getChangeMessage( ) );
237 daoUtil.setString( nIndex++, attributeChange.getAuthorType( ).name( ) );
238 daoUtil.setString( nIndex++, attributeChange.getAuthorName( ) );
239 daoUtil.setString( nIndex++, attributeChange.getClientCode( ) );
240 daoUtil.setInt( nIndex++, attributeChange.getIdIdentity( ) );
241 daoUtil.setString( nIndex++, attributeChange.getAttributeKey( ) );
242 daoUtil.setString( nIndex++, attributeChange.getAttributeValue( ) );
243 daoUtil.setString( nIndex++, attributeChange.getCertificationProcessus( ) );
244 daoUtil.setTimestamp( nIndex++, attributeChange.getCertificationDate( ) );
245 daoUtil.setTimestamp( nIndex++, attributeChange.getModificationDate( ) );
246 daoUtil.setString( nIndex, objectMapper.writeValueAsString( attributeChange.getMetadata( ) ) );
247
248 daoUtil.executeUpdate( );
249 }
250 catch( JsonProcessingException e )
251 {
252 throw new IdentityStoreException( e.getMessage( ), e );
253 }
254 }
255
256 @Override
257 public List<AttributeChange> getAttributeChangeHistory( int nIdentityId, Plugin plugin ) throws IdentityStoreException
258 {
259 final List<AttributeChange> listAttributeChange = new ArrayList<>( );
260 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE_HISTORY, plugin ) )
261 {
262 daoUtil.setInt( 1, nIdentityId );
263 daoUtil.executeQuery( );
264
265 while ( daoUtil.next( ) )
266 {
267 listAttributeChange.add( this.getAttributeChange( daoUtil ) );
268 }
269
270 return listAttributeChange;
271 }
272 catch( JsonProcessingException e )
273 {
274 throw new IdentityStoreException( e.getMessage( ), e );
275 }
276 }
277
278 @Override
279 public List<AttributeChange> getAttributeChangeHistory( String customerId, Plugin plugin ) throws IdentityStoreException
280 {
281 final List<AttributeChange> listAttributeChange = new ArrayList<>( );
282 try ( final DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE_HISTORY_BY_CUSTOMER_ID, plugin ) )
283 {
284 daoUtil.setString( 1, customerId );
285 daoUtil.executeQuery( );
286
287 while ( daoUtil.next( ) )
288 {
289 listAttributeChange.add( this.getAttributeChange( daoUtil ) );
290 }
291
292 return listAttributeChange;
293 }
294 catch( JsonProcessingException e )
295 {
296 throw new IdentityStoreException( e.getMessage( ), e );
297 }
298 }
299
300 public IdentityAttribute getIdentityAttribute( final DAOUtil daoUtil )
301 {
302 final IdentityAttributeiness/identity/IdentityAttribute.html#IdentityAttribute">IdentityAttribute identityAttribute = new IdentityAttribute( );
303 identityAttribute.setIdIdentity( daoUtil.getInt( "id_identity" ) );
304 identityAttribute.setValue( daoUtil.getString( "attribute_value" ) );
305 identityAttribute.setLastUpdateDate( daoUtil.getTimestamp( "lastupdate_date" ) );
306 identityAttribute.setLastUpdateClientCode( daoUtil.getString( "lastupdate_client" ) );
307
308 final int nCertificateId = daoUtil.getInt( "id_certification" );
309 if ( nCertificateId != 0 )
310 {
311 final AttributeCertificatebusiness/attribute/AttributeCertificate.html#AttributeCertificate">AttributeCertificate certificate = new AttributeCertificate( );
312 certificate.setId( nCertificateId );
313 certificate.setCertifierCode( daoUtil.getString( "certifier_code" ) );
314 certificate.setCertifierName( certificate.getCertifierCode( ) );
315 certificate.setCertificateDate( daoUtil.getTimestamp( "certificate_date" ) );
316 certificate.setExpirationDate( daoUtil.getTimestamp( "expiration_date" ) );
317 identityAttribute.setCertificate( certificate );
318 }
319
320 final AttributeKeyystore/business/attribute/AttributeKey.html#AttributeKey">AttributeKey attributeKey = new AttributeKey( );
321 identityAttribute.setAttributeKey( attributeKey );
322 attributeKey.setId( daoUtil.getInt( "attribute_key_id" ) );
323 attributeKey.setKeyName( daoUtil.getString( "attribute_key_name" ) );
324 attributeKey.setKeyType( KeyType.valueOf( daoUtil.getInt( "attribute_key_type" ) ) );
325 attributeKey.setKeyWeight( daoUtil.getInt( "attribute_key_weight" ) );
326 attributeKey.setName( daoUtil.getString( "attribute_name" ) );
327 attributeKey.setDescription( daoUtil.getString( "attribute_description" ) );
328 attributeKey.setCommonSearchKeyName( daoUtil.getString( "common_search_key" ) );
329 attributeKey.setValidationErrorMessage( daoUtil.getString( "validation_error_message" ) );
330 attributeKey.setValidationRegex( daoUtil.getString( "validation_regex" ) );
331 attributeKey.setPivot( daoUtil.getBoolean( "pivot" ) );
332 attributeKey.setCertifiable( daoUtil.getBoolean( "certifiable" ) );
333 attributeKey.setMandatoryForCreation( daoUtil.getBoolean( "mandatory_for_creation" ) );
334 return identityAttribute;
335 }
336
337 public AttributeChange getAttributeChange( final DAOUtil daoUtil ) throws JsonProcessingException
338 {
339 final AttributeChange attributeChange = new AttributeChange( );
340 int nIndex = 1;
341 attributeChange.setId( daoUtil.getInt( nIndex++ ) );
342 attributeChange.setChangeType( AttributeChangeType.valueOf( daoUtil.getInt( nIndex++ ) ) );
343 attributeChange.setChangeSatus( daoUtil.getString( nIndex++ ) );
344 attributeChange.setChangeMessage( daoUtil.getString( nIndex++ ) );
345 attributeChange.setAuthorType( AuthorType.valueOf( daoUtil.getString( nIndex++ ) ) );
346 attributeChange.setAuthorName( daoUtil.getString( nIndex++ ) );
347 attributeChange.setClientCode( daoUtil.getString( nIndex++ ) );
348 attributeChange.setIdIdentity( daoUtil.getInt( nIndex++ ) );
349 attributeChange.setAttributeKey( daoUtil.getString( nIndex++ ) );
350 attributeChange.setAttributeValue( daoUtil.getString( nIndex++ ) );
351 attributeChange.setCertificationProcessus( daoUtil.getString( nIndex++ ) );
352 attributeChange.setCertificationDate( daoUtil.getTimestamp( nIndex++ ) );
353 attributeChange.setModificationDate( daoUtil.getTimestamp( nIndex++ ) );
354 final String jsonMap = daoUtil.getString( nIndex );
355 if ( StringUtils.isNotEmpty( jsonMap ) )
356 {
357 final Map<String, String> mapMetaData = objectMapper.readValue( jsonMap, new TypeReference<Map<String, String>>( )
358 {
359 } );
360 attributeChange.getMetadata( ).clear( );
361 if ( mapMetaData != null && !mapMetaData.isEmpty( ) )
362 {
363 attributeChange.getMetadata( ).putAll( mapMetaData );
364 }
365 }
366 return attributeChange;
367 }
368 }