View Javadoc
1   /*
2    * Copyright (c) 2002-2020, City of Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.plugins.document.business.attributes;
35  
36  import fr.paris.lutece.plugins.document.business.DocumentType;
37  import fr.paris.lutece.util.sql.DAOUtil;
38  
39  import java.util.ArrayList;
40  import java.util.Collection;
41  import java.util.List;
42  
43  
44  /**
45   * This class provides Data Access methods for DocumentAttribute objects
46   */
47  public final class DocumentAttributeDAO implements IDocumentAttributeDAO
48  {
49      // Constants
50      private static final String SQL_QUERY_NEW_PK = " SELECT max( id_document_attr ) FROM document_type_attr ";
51      private static final String SQL_QUERY_INSERT = " INSERT INTO document_type_attr ( id_document_attr, code_document_type, code_attr_type, code, document_type_attr_name, description, attr_order, required, searchable ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";
52      private static final String SQL_QUERY_DELETE = " DELETE FROM document_type_attr WHERE id_document_attr = ?  ";
53      private static final String SQL_QUERY_UPDATE = " UPDATE document_type_attr SET id_document_attr = ?, code_document_type = ?, code_attr_type = ?, code = ?, document_type_attr_name = ?, description = ?, attr_order = ?, required = ?, searchable = ? WHERE id_document_attr = ?  ";
54      private static final String SQL_QUERY_SELECTALL_ATTRIBUTES = " SELECT a.id_document_attr, a.code_document_type," +
55          " a.code_attr_type, a.code, " +
56          " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable " +
57          " FROM document_type_attr a, document_attr_type b" + " WHERE a.code_attr_type =  b.code_attr_type" +
58          " AND a.code_document_type = ? ORDER BY  a.attr_order";
59      private static final String SQL_QUERY_SELECT_ATTRIBUTE = " SELECT a.id_document_attr, a.code_document_type," +
60          " a.code_attr_type, a.code, " +
61          " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable " +
62          " FROM document_type_attr a, document_attr_type b" + " WHERE a.code_attr_type =  b.code_attr_type" +
63          " AND a.id_document_attr = ? ";
64      private static final String SQL_QUERY_SELECTALL_ATTRIBUTES_OF_DOCUMENT_TYPE = " SELECT DISTINCT a.id_document_attr, " +
65          " a.code_document_type, a.code_attr_type, a.code, " +
66          " a.document_type_attr_name, a.description, a.attr_order, a.required, a.searchable " +
67          " FROM document_type_attr a" + " WHERE a.code_document_type = ?" + " ORDER BY  a.attr_order";
68      private static final String SQL_QUERY_INSERT_PARAMETER_VALUES = "INSERT INTO document_type_attr_parameters ( id_document_attr, parameter_name, id_list_parameter, parameter_value )" +
69          "VALUES ( ?, ?, ?, ? ) ";
70      private static final String SQL_QUERY_SELECT_PARAMETERS = "SELECT DISTINCT parameter_name FROM document_type_attr_parameters WHERE id_document_attr = ? ";
71      private static final String SQL_QUERY_SELECT_PARAMETER_VALUES = "SELECT parameter_value FROM document_type_attr_parameters " +
72          "WHERE id_document_attr = ? AND parameter_name = ? ";
73      private static final String SQL_QUERY_DELETE_PARAMETER_VALUES = "DELETE FROM document_type_attr_parameters WHERE id_document_attr = ? AND parameter_name = ? ";
74      private static final String SQL_QUERY_DELETE_PARAMETERS_VALUES = "DELETE FROM document_type_attr_parameters WHERE id_document_attr = ? ";
75      private static final String SQL_QUERY_INSERT_REGULAR_EXPRESSION = "INSERT INTO document_type_attr_verify_by(id_document_attr,id_expression) VALUES(?,?)";
76      private static final String SQL_QUERY_DELETE_REGULAR_EXPRESSION = "DELETE FROM document_type_attr_verify_by WHERE id_document_attr=? AND id_expression=?";
77      private static final String SQL_QUERY_DELETE_REGULAR_EXPRESSIONS = "DELETE FROM document_type_attr_verify_by WHERE id_document_attr=?";
78      private static final String SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_ATTRIBUTE = "SELECT id_expression FROM document_type_attr_verify_by WHERE id_document_attr=?";
79  
80      /**
81       * Generates a new primary key
82       * @return The new primary key
83       */
84      private int newPrimaryKey(  )
85      {
86          DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK );
87          daoUtil.executeQuery(  );
88  
89          int nKey;
90  
91          if ( !daoUtil.next(  ) )
92          {
93              // if the table is empty
94              nKey = 1;
95          }
96  
97          nKey = daoUtil.getInt( 1 ) + 1;
98  
99          daoUtil.free(  );
100 
101         return nKey;
102     }
103 
104     /**
105      * Insert a new record in the table.
106      *
107      * @param documentAttribute The documentAttribute object
108      */
109     public synchronized void insert( DocumentAttribute documentAttribute )
110     {
111         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
112         documentAttribute.setId( newPrimaryKey(  ) );
113         daoUtil.setInt( 1, documentAttribute.getId(  ) );
114         daoUtil.setString( 2, documentAttribute.getCodeDocumentType(  ) );
115         daoUtil.setString( 3, documentAttribute.getCodeAttributeType(  ) );
116         daoUtil.setString( 4, documentAttribute.getCode(  ) );
117         daoUtil.setString( 5, documentAttribute.getName(  ) );
118         daoUtil.setString( 6, documentAttribute.getDescription(  ) );
119         daoUtil.setInt( 7, documentAttribute.getAttributeOrder(  ) );
120         daoUtil.setInt( 8, documentAttribute.isRequired(  ) ? 1 : 0 );
121         daoUtil.setInt( 9, documentAttribute.isSearchable(  ) ? 1 : 0 );
122 
123         daoUtil.executeUpdate(  );
124         daoUtil.free(  );
125         // Insert parameters
126         insertAttributeParameters( documentAttribute );
127     }
128 
129     /**
130      * Load the data of DocumentAttribute from the table
131      * @param nAttributeId The attribute Id
132      * @return the instance of the DocumentAttribute
133      */
134     public DocumentAttribute load( int nAttributeId )
135     {
136         DocumentAttribute documentAttribute = null;
137         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ATTRIBUTE );
138         daoUtil.setInt( 1, nAttributeId );
139         daoUtil.executeQuery(  );
140 
141         if ( daoUtil.next(  ) )
142         {
143             documentAttribute = new DocumentAttribute(  );
144             documentAttribute.setId( daoUtil.getInt( 1 ) );
145             documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
146             documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
147             documentAttribute.setCode( daoUtil.getString( 4 ) );
148             documentAttribute.setName( daoUtil.getString( 5 ) );
149             documentAttribute.setDescription( daoUtil.getString( 6 ) );
150             documentAttribute.setAttributeOrder( daoUtil.getInt( 7 ) );
151             documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
152             documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
153         }
154 
155         daoUtil.free(  );
156 
157         return documentAttribute;
158     }
159 
160     /**
161      * Delete a record from the table
162      * @param nAttributeId The DocumentAttribute Id
163      */
164     public void delete( int nAttributeId )
165     {
166         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
167         daoUtil.setInt( 1, nAttributeId );
168 
169         daoUtil.executeUpdate(  );
170         daoUtil.free(  );
171         deleteParameters( nAttributeId );
172         deleteRegularExpressions( nAttributeId );
173     }
174 
175     /**
176      * Delete a record from the table
177      * @param nAttributeId The DocumentAttribute Id
178      */
179     private void deleteParameters( int nAttributeId )
180     {
181         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETERS_VALUES );
182         daoUtil.setInt( 1, nAttributeId );
183 
184         daoUtil.executeUpdate(  );
185         daoUtil.free(  );
186     }
187 
188     /**
189      * Delete a record from the table
190      * @param nAttributeId The DocumentAttribute Id
191      * @param strParameterName The parameter name
192      */
193     private void deleteParameter( int nAttributeId, String strParameterName )
194     {
195         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_PARAMETER_VALUES );
196         daoUtil.setInt( 1, nAttributeId );
197         daoUtil.setString( 2, strParameterName );
198 
199         daoUtil.executeUpdate(  );
200         daoUtil.free(  );
201     }
202 
203     /**
204      * Update the record in the table
205      * @param documentAttribute The document attribute
206      */
207     public void store( DocumentAttribute documentAttribute )
208     {
209         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
210         daoUtil.setInt( 1, documentAttribute.getId(  ) );
211         daoUtil.setString( 2, documentAttribute.getCodeDocumentType(  ) );
212         daoUtil.setString( 3, documentAttribute.getCodeAttributeType(  ) );
213         daoUtil.setString( 4, documentAttribute.getCode(  ) );
214         daoUtil.setString( 5, documentAttribute.getName(  ) );
215         daoUtil.setString( 6, documentAttribute.getDescription(  ) );
216         daoUtil.setInt( 7, documentAttribute.getAttributeOrder(  ) );
217         daoUtil.setInt( 8, documentAttribute.isRequired(  ) ? 1 : 0 );
218         daoUtil.setInt( 9, documentAttribute.isSearchable(  ) ? 1 : 0 );
219         daoUtil.setInt( 10, documentAttribute.getId(  ) );
220 
221         daoUtil.executeUpdate(  );
222         daoUtil.free(  );
223 
224         // Update parameters
225         deleteParameters( documentAttribute.getId(  ) );
226         insertAttributeParameters( documentAttribute );
227     }
228 
229     /**
230      * Add attributes to a document
231      * @param documentType The document Type
232      */
233     public void selectAttributesByDocumentType( DocumentType documentType )
234     {
235         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES );
236         daoUtil.setString( 1, documentType.getCode(  ) );
237         daoUtil.executeQuery(  );
238 
239         int nOrder = 1;
240 
241         while ( daoUtil.next(  ) )
242         {
243             DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute(  );
244             documentAttribute.setId( daoUtil.getInt( 1 ) );
245             documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
246             documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
247             documentAttribute.setCode( daoUtil.getString( 4 ) );
248             documentAttribute.setName( daoUtil.getString( 5 ) );
249             documentAttribute.setDescription( daoUtil.getString( 6 ) );
250             documentAttribute.setAttributeOrder( nOrder );
251             documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
252             documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
253 
254             documentType.addAttribute( documentAttribute );
255             nOrder++;
256         }
257 
258         daoUtil.free(  );
259     }
260 
261     /**
262      * Get all attributes of document type
263      * @param codeDocumentType The code document Type
264      * @return listDocumentAttributes The list of all attributes of selected code document type
265      */
266     public List<DocumentAttribute> selectAllAttributesOfDocumentType( String codeDocumentType )
267     {
268         List<DocumentAttribute> listDocumentAttributes = new ArrayList<DocumentAttribute>(  );
269         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ATTRIBUTES_OF_DOCUMENT_TYPE );
270         daoUtil.setString( 1, codeDocumentType );
271         daoUtil.executeQuery(  );
272 
273         while ( daoUtil.next(  ) )
274         {
275             DocumentAttribute/attributes/DocumentAttribute.html#DocumentAttribute">DocumentAttribute documentAttribute = new DocumentAttribute(  );
276             documentAttribute.setId( daoUtil.getInt( 1 ) );
277             documentAttribute.setCodeDocumentType( daoUtil.getString( 2 ) );
278             documentAttribute.setCodeAttributeType( daoUtil.getString( 3 ) );
279             documentAttribute.setCode( daoUtil.getString( 4 ) );
280             documentAttribute.setName( daoUtil.getString( 5 ) );
281             documentAttribute.setDescription( daoUtil.getString( 6 ) );
282             documentAttribute.setAttributeOrder( daoUtil.getInt( 7 ) );
283             documentAttribute.setRequired( daoUtil.getInt( 8 ) != 0 );
284             documentAttribute.setSearchable( daoUtil.getInt( 9 ) != 0 );
285             listDocumentAttributes.add( documentAttribute );
286         }
287 
288         daoUtil.free(  );
289 
290         return listDocumentAttributes;
291     }
292 
293     // Parameters
294     private void insertAttributeParameters( DocumentAttribute documentAttribute )
295     {
296         for ( AttributeTypeParameter parameter : documentAttribute.getParameters(  ) )
297         {
298             deleteParameter( documentAttribute.getId(  ), parameter.getName(  ) );
299 
300             int i = 0;
301 
302             for ( String value : parameter.getValueList(  ) )
303             {
304                 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_PARAMETER_VALUES );
305                 daoUtil.setInt( 1, documentAttribute.getId(  ) );
306                 daoUtil.setString( 2, parameter.getName(  ) );
307                 daoUtil.setInt( 3, i++ );
308                 daoUtil.setString( 4, value );
309 
310                 daoUtil.executeUpdate(  );
311                 daoUtil.free(  );
312             }
313         }
314     }
315 
316     /**
317      * Gets Attribute parameters values
318      * @param nAttributeId The attribute Id
319      * @return List of attribute parameters values
320      */
321     public List<AttributeTypeParameter> selectAttributeParametersValues( int nAttributeId )
322     {
323         ArrayList<AttributeTypeParameter> listParameters = new ArrayList<AttributeTypeParameter>(  );
324         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETERS );
325         daoUtil.setInt( 1, nAttributeId );
326         daoUtil.executeQuery(  );
327 
328         while ( daoUtil.next(  ) )
329         {
330             AttributeTypeParameteress/attributes/AttributeTypeParameter.html#AttributeTypeParameter">AttributeTypeParameter parameter = new AttributeTypeParameter(  );
331             parameter.setName( daoUtil.getString( 1 ) );
332             parameter.setValueList( getAttributeParameterValues( nAttributeId, parameter.getName(  ) ) );
333             listParameters.add( parameter );
334         }
335 
336         daoUtil.free(  );
337 
338         return listParameters;
339     }
340 
341     /**
342      * Returns the parameter value of an attribute
343      * @param nAttributeId The attribute Id
344      * @param strParameterName The parameter name
345      * @return The parameter values of an attribute
346      */
347     public List<String> getAttributeParameterValues( int nAttributeId, String strParameterName )
348     {
349         List<String> listValues = new ArrayList<String>(  );
350         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PARAMETER_VALUES );
351         daoUtil.setInt( 1, nAttributeId );
352         daoUtil.setString( 2, strParameterName );
353         daoUtil.executeQuery(  );
354 
355         while ( daoUtil.next(  ) )
356         {
357             listValues.add( daoUtil.getString( 1 ) );
358         }
359 
360         daoUtil.free(  );
361 
362         return listValues;
363     }
364 
365     /**
366      * Inserts an association between an attribute and a regular expression
367      *
368      * @param nIdAttribute The identifier of the document attribute
369      * @param nIdExpression The identifier of the regular expression
370      */
371     public void insertRegularExpression( int nIdAttribute, int nIdExpression )
372     {
373         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_REGULAR_EXPRESSION );
374         daoUtil.setInt( 1, nIdAttribute );
375         daoUtil.setInt( 2, nIdExpression );
376         daoUtil.executeUpdate(  );
377         daoUtil.free(  );
378     }
379 
380     /**
381      * Deletes an association between an attribute and a regular expression
382      *
383      * @param nIdAttribute The identifier of the document attribute
384      * @param nIdExpression The identifier of the regular expression
385      */
386     public void deleteRegularExpression( int nIdAttribute, int nIdExpression )
387     {
388         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSION );
389         daoUtil.setInt( 1, nIdAttribute );
390         daoUtil.setInt( 2, nIdExpression );
391         daoUtil.executeUpdate(  );
392         daoUtil.free(  );
393     }
394 
395     /**
396      * Deletes all association between an attribute and the regular expression
397      *
398      * @param nIdAttribute The identifier of the document attribute
399      */
400     private void deleteRegularExpressions( int nIdAttribute )
401     {
402         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_REGULAR_EXPRESSIONS );
403         daoUtil.setInt( 1, nIdAttribute );
404         daoUtil.executeUpdate(  );
405         daoUtil.free(  );
406     }
407 
408     /**
409      * Loads all regular expression key associated to the attribute and returns them into a collection
410      *
411      * @param nIdAttribute The identifier of the document attribute
412      * @return A collection of regular expression key
413      */
414     public Collection<Integer> selectListRegularExpressionKeyByIdAttribute( int nIdAttribute )
415     {
416         Collection<Integer> colRegularExpression = new ArrayList<Integer>(  );
417 
418         DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_REGULAR_EXPRESSION_BY_ID_ATTRIBUTE );
419         daoUtil.setInt( 1, nIdAttribute );
420         daoUtil.executeQuery(  );
421 
422         while ( daoUtil.next(  ) )
423         {
424             colRegularExpression.add( daoUtil.getInt( 1 ) );
425         }
426 
427         daoUtil.free(  );
428 
429         return colRegularExpression;
430     }
431 }