JahiaContainerUtilsDB


1   //
2   //                                   ____.
3   //                       __/\ ______|    |__/\.     _______
4   //            __   .____|    |       \   |    +----+       \
5   //    _______|  /--|    |    |    -   \  _    |    :    -   \_________
6   //   \\______: :---|    :    :           |    :    |         \________>
7   //           |__\---\_____________:______:    :____|____:_____\
8   //                                      /_____|
9   //
10  //                 . . . i n   j a h i a   w e   t r u s t . . .
11  //
12  
13  package org.jahia.services.containers;
14  
15  import java.sql.Connection  ;
16  import java.sql.PreparedStatement  ;
17  import java.sql.ResultSet  ;
18  import java.sql.SQLException  ;
19  import java.sql.Statement  ;
20  import java.util.ArrayList  ;
21  import java.util.HashMap  ;
22  import java.util.Hashtable  ;
23  import java.util.List  ;
24  import java.util.Map  ;
25  import java.util.Set  ;
26  import java.util.SortedSet  ;
27  import java.util.TreeSet  ;
28  import java.util.Vector  ;
29  
30  import org.jahia.data.containers.JahiaContainerDefinition;
31  import org.jahia.exceptions.JahiaException;
32  import org.jahia.registries.JahiaContainerDefinitionsRegistry;
33  import org.jahia.registries.ServicesRegistry;
34  import org.jahia.services.cache.Cache;
35  import org.jahia.services.cache.CacheFactory;
36  import org.jahia.services.database.ConnectionDispenser;
37  import org.jahia.services.pages.ContentPage;
38  import org.jahia.services.version.EntryLoadRequest;
39  import org.jahia.utils.JahiaTools;
40  
41  /**
42   * JahiaContainerUtilsDB
43   *
44   * @author Eric Vassalli
45   *         Various utilities for containers
46   */
47  
48  public class JahiaContainerUtilsDB {
49  
50      private static org.apache.log4j.Logger logger =
51              org.apache.log4j.Logger.getLogger (JahiaContainerUtilsDB.class);
52  
53      private static final String   CTNLISTID_BYPAGEANDCTNDEF_QUERY = "SELECT id_jahia_ctn_lists FROM jahia_ctn_lists WHERE ((pageid_jahia_ctn_lists=?) AND (ctndefid_jahia_ctn_lists=?))";
54  
55      // the Sub Containerlists IDs by Container cache name.
56      public static final String   SUB_CONTAINERLIST_IDS_BY_CONTAINER_CACHE = "SubContainerListIDsByContainerCache";
57      // the Container IDs by Container List cache name.
58      public static final String   CONTAINER_IDS_BY_CONTAINERLIST_CACHE = "ContainerIDsByContainerListCache";
59      private Cache subContainerListIDsByContainerCache;
60      private Cache containerIDsByContainerListCache;
61  
62  
63      private JahiaContainerUtilsDB () {
64          try {
65              subContainerListIDsByContainerCache = CacheFactory.createCache(SUB_CONTAINERLIST_IDS_BY_CONTAINER_CACHE);
66              containerIDsByContainerListCache = CacheFactory.createCache(CONTAINER_IDS_BY_CONTAINERLIST_CACHE);
67          } catch ( JahiaException je ){
68              logger.debug(" Exception creating cache",je);
69          }
70  
71      } // end constructor
72  
73      private static JahiaContainerUtilsDB singletonInstance = null;
74  
75      /**
76       * Return an instance of this class
77       */
78      public static synchronized JahiaContainerUtilsDB getInstance () {
79          if (singletonInstance == null) {
80              singletonInstance = new JahiaContainerUtilsDB ();
81          }
82          return singletonInstance;
83      }
84  
85      /**
86       * finds a container list id from its name and page id
87       *
88       * @param listName the container list name
89       * @param pageID   the page id
90       *
91       * @return a container list ID
92       *
93       * @throws raises a critical JahiaException if a data access error occurs
94       * @throws raises a critical JahiaException if cannot free resources
95       * @throws raises a warning JahiaException if cannot find id
96       */
97      public int db_get_container_list_id (String   listName, int pageID)
98              throws JahiaException {
99          Connection   dbConn = null;
100         PreparedStatement   stmt = null;
101         ResultSet   rs = null;
102         int listID = -1;
103         int ctndefid = -1;
104         try {
105 
106             // gets page definition id
107             // gets the page
108             ContentPage remotePage = ServicesRegistry.getInstance ()
109                     .getJahiaPageService ().lookupContentPage (pageID, false);
110 
111             if (remotePage == null) {
112                 String   errorMsg = "Container " + listName + " is being loaded from an unexisting page (" + pageID + ")";
113                 logger.debug (errorMsg);
114                 return -1;
115             }
116 
117             // gets connexion
118             dbConn = ConnectionDispenser.getConnection ();
119 
120             /* Replacing this code with a lookup in the container definition
121                registry
122             // gets container definition id
123             stmt = dbConn.prepareStatement(CTNDEFID_BYNAMEANDSITE_QUERY);
124             stmt.setString(1, listName);
125             stmt.setInt(2, remotePage.getJahiaID());
126             rs = stmt.executeQuery();
127             if (rs.next()) {
128                 ctndefid = rs.getInt( "id_jahia_ctn_def" );
129             } else {
130                 String errorMsg = "Container " + listName + " is not defined";
131                 logger.debug(errorMsg);
132                 return -1;
133             }
134             */
135             JahiaContainerDefinition ctnDef = JahiaContainerDefinitionsRegistry.getInstance ()
136                     .getDefinition (remotePage.getJahiaID (), listName);
137             if (ctnDef == null) {
138                 String   errorMsg = "Container " + listName + " is not defined";
139                 logger.debug (errorMsg);
140                 return -1;
141             }
142             ctndefid = ctnDef.getID ();
143 
144             // gets container list id
145             stmt = dbConn.prepareStatement (CTNLISTID_BYPAGEANDCTNDEF_QUERY);
146             stmt.setInt (1, pageID);
147             stmt.setInt (2, ctndefid);
148             rs = stmt.executeQuery ();
149             if (rs.next ()) {
150                 listID = rs.getInt ("id_jahia_ctn_lists");
151             } else {
152                 return -1;
153             }
154         } catch (SQLException   se) {
155             String   errorMsg = "Error in db_get_container_list_id : " + se.getMessage ();
156             logger.debug (errorMsg + " -> BAILING OUT");
157             throw new JahiaException ("Cannot load containers from the database",
158                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY,
159                     se);
160         } finally {
161             try {
162                 if (stmt != null) stmt.close ();
163             } catch (SQLException   ex) {
164                 logger.debug ("Cannot free resources", ex);
165             }
166         }
167 
168         return listID;
169     } // db_get_container_list_id
170 
171     /**
172      * loads all the container list ids by their page id
173      *
174      * @param pageID the page ID
175      * @param defID  the container definition id
176      *
177      * @return a Vector of container list IDs
178      *
179      * @throws raises a JahiaException if a data access error occurs
180      * @throws raises a JahiaException if cannot free resources
181      */
182     public Vector   db_get_page_container_list_ids (int pageID, EntryLoadRequest loadVersion)
183             throws JahiaException {
184         Connection   dbConn = null;
185         PreparedStatement   stmt = null;
186         ResultSet   rs = null;
187         Vector   theList = new Vector   ();
188         try {
189             // gets connexion
190             dbConn = ConnectionDispenser.getConnection ();
191 
192             String   sqlQuery = null;
193 
194             // if we want to load the staged version
195             if (loadVersion.isStaging ()) {
196                 sqlQuery = new String   ("SELECT listid_jahia_ctn_entries,workflow_state,version_id "
197                         + "FROM jahia_ctn_entries "
198                         + "WHERE pageid_jahia_ctn_entries=? "
199                         + "AND workflow_state>0 "
200                         + "ORDER BY listid_jahia_ctn_entries ASC, workflow_state DESC");
201   //             logger.debug("Loading staged version...");
202                 stmt = dbConn.prepareStatement (sqlQuery);
203                 stmt.setInt(1, pageID);              
204                 rs = stmt.executeQuery ();
205                 int ctnID, oldCtnID, versionID;
206                 oldCtnID = -1;
207                 while (rs.next ()) {
208                     ctnID = rs.getInt ("listid_jahia_ctn_entries");
209                     versionID = rs.getInt ("version_id");
210                     // this is a new container, we add it in the list, also it has not
211                     // been deleted (rights!=-1)
212                     if ((ctnID != oldCtnID)) {
213                         if (loadVersion.isWithMarkedForDeletion ()) {
214                             theList.add (new Integer   (ctnID));
215                         } else {
216                             if (versionID != -1) {
217                                 theList.add (new Integer   (ctnID));
218                             }
219                         }
220                     }
221                     oldCtnID = ctnID;
222                 }
223             } else
224             // if we want to load the version ID
225                 if (loadVersion.isVersioned ()) {
226                     sqlQuery = new String   ("SELECT listid_jahia_ctn_entries,version_id "
227                             + "FROM jahia_ctn_entries "
228                             + "WHERE pageid_jahia_ctn_entries=" + pageID + " "
229                             + " AND workflow_state<=1 "
230                             + "AND version_id<=" + loadVersion.getVersionID () + " AND version_id > -1 "
231                             + "ORDER BY listid_jahia_ctn_entries ASC, version_id DESC");
232 //              logger.debug("Loading staged version...");
233                     rs = stmt.executeQuery (sqlQuery);
234                     int ctnID, oldCtnID, versionID;
235                     oldCtnID = -1;
236                     while (rs.next ()) {
237                         ctnID = rs.getInt ("listid_jahia_ctn_entries");
238                         if ( !theList.contains(new Integer  (ctnID)) ){
239                             versionID = rs.getInt("version_id");
240                             // this is a new container, we add it in the list, also it has not
241                             // been deleted (verID!=-1)
242                             if ( (ctnID != oldCtnID) && (versionID != -1)) {
243                                 theList.add(new Integer  (ctnID));
244                             }
245                         }
246                         oldCtnID = ctnID;
247                     }
248                 } else
249                 // we do the normal request
250                 {
251                     sqlQuery = new String   ("SELECT DISTINCT listid_jahia_ctn_entries FROM jahia_ctn_entries "
252                             + "WHERE pageid_jahia_ctn_entries=" + pageID + " "
253                             + " AND workflow_state=1 "
254                             + " ORDER BY listid_jahia_ctn_entries ASC");
255                     rs = stmt.executeQuery (sqlQuery);
256                     while (rs.next ()) {
257                         theList.add (new Integer   (rs.getInt ("listid_jahia_ctn_entries")));
258                     }
259                 }
260         } catch (SQLException   se) {
261             String   errorMsg = "Error in db_get_page_container_list_ids : " + se.getMessage ();
262             logger.debug (errorMsg + " -> BAILING OUT");
263             throw new JahiaException ("Cannot load containers from the database",
264                     errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY,
265                     se);
266         } finally {
267             try {
268 
269                 if (stmt != null) stmt.close ();
270             } catch (SQLException   ex) {
271                 logger.debug ("Cannot free resources", ex);
272             }
273         }
274 
275         return theList;
276     }
277 
278     /**
279      * Retrieves the containerListIDs for container lists that are directly
280      * attached to the page (meaning that they are not container lists
281      * contained with a container).
282      *
283      * @param pageID      the page for which to retrieve top level container list
284      *                    IDs
285      * @param loadVersion the entryState for which to request the container
286      *                    list IDs
287      *
288      * @return a Vector of Integer objects that contain the IDs of the
289      *         top level container lists in a page.
290      *
291      * @throws JahiaException if there was an error while communicating with
292      *                        the database.
293      */
294     public Vector   getPageTopLevelContainerListIDs (int pageID, EntryLoadRequest loadVersion)
295             throws JahiaException {
296         Connection   dbConn = null;
297         PreparedStatement   stmt = null;
298         ResultSet   rs = null;
299         Vector   theList = new Vector   ();
300         try {
301             // gets connexion
302             dbConn = ConnectionDispenser.getConnection ();
303 
304             // if we want to load the staged version
305             if (loadVersion.isStaging ()) {
306                 stmt =
307                         dbConn.prepareStatement (
308                                 "SELECT id_jahia_ctn_lists, version_id FROM jahia_ctn_lists WHERE pageid_jahia_ctn_lists=? AND parententryid_jahia_ctn_lists=0 AND workflow_state>0 ORDER BY id_jahia_ctn_lists, workflow_state DESC");
309                 stmt.setInt (1, pageID);
310                 rs = stmt.executeQuery ();
311                 int ctnListID, oldCtnListID, versionID;
312 
313                 oldCtnListID = -1;
314                 while (rs.next ()) {
315                     ctnListID = rs.getInt ("id_jahia_ctn_lists");
316                     versionID = rs.getInt ("version_id");
317                     if ( !theList.contains(new Integer  (ctnListID)) ){
318                         // this is a new container, we add it in the list, also it has not
319                         // been deleted (rights!=-1)
320                         if (ctnListID != oldCtnListID) {
321                             if (loadVersion.isWithMarkedForDeletion()) {
322                                 theList.add(new Integer  (ctnListID));
323                             }
324                             else {
325                                 if (versionID != -1) {
326                                     theList.add(new Integer  (ctnListID));
327                                 }
328                             }
329                         }
330                     }
331                     oldCtnListID = ctnListID;
332                 }
333             } else
334             // if we want to load the version ID
335                 if (loadVersion.isVersioned ()) {
336                     stmt =
337                             dbConn.prepareStatement (
338                                     "SELECT id_jahia_ctn_lists, version_id FROM jahia_ctn_lists WHERE pageid_jahia_ctn_lists=? AND parententryid_jahia_ctn_lists=0 AND workflow_state < 1 AND version_id<=? AND version_id > -1 ORDER BY id_jahia_ctn_lists, version_id DESC");
339                     stmt.setInt (1, pageID);
340                     stmt.setInt (2, loadVersion.getVersionID ());
341                     rs = stmt.executeQuery ();
342                     int ctnListID, oldCtnListID, versionID;
343                     oldCtnListID = -1;
344                     while (rs.next ()) {
345                         ctnListID = rs.getInt ("id_jahia_ctn_lists");
346                         versionID = rs.getInt ("version_id");
347                         if ( !theList.contains(new Integer  (ctnListID)) ){
348                             // this is a new container, we add it in the list, also it has not
349                             // been deleted (verID!=-1)
350                             if ( (ctnListID != oldCtnListID) && (versionID != -1)) {
351                                 theList.add(new Integer  (ctnListID));
352                             }
353                         }
354                         oldCtnListID = ctnListID;
355                     }
356                 } else
357                 // we do the normal request
358                 {
359                     stmt =
360                             dbConn.prepareStatement (
361                                     "SELECT DISTINCT id_jahia_ctn_lists FROM jahia_ctn_lists WHERE pageid_jahia_ctn_lists=? AND parententryid_jahia_ctn_lists=0 AND workflow_state=1 ORDER BY id_jahia_ctn_lists ASC");
362                     stmt.setInt (1, pageID);
363                     rs = stmt.executeQuery ();
364                     while (rs.next ()) {
365                         theList.add (new Integer   (rs.getInt ("id_jahia_ctn_lists")));
366                     }
367                 }
368         } catch (SQLException   se) {
369             String   errorMsg = "Error in getPageTopLevelContainerListIDs : " + se.getMessage ();
370             logger.debug (errorMsg + " -> BAILING OUT", se);
371             throw new JahiaException ("Cannot load containers from the database",
372                     errorMsg, JahiaException.DATABASE_ERROR,
373                     JahiaException.CRITICAL_SEVERITY, se);
374         } finally {
375             try {
376 
377                 if (stmt != null) stmt.close ();
378             } catch (SQLException   ex) {
379                 logger.debug ("Cannot free resources", ex);
380             }
381         }
382 
383         return theList;
384     }
385 
386     /**
387      * Returns the IDs of the container lists contained within a container.
388      *
389      * @param containerID the container ID for which to retrieve the sub
390      *                    container list IDs
391      * @param loadVersion the entryState for which to retrieve the sub container
392      *                    list IDs
393      *
394      * @return a Vector of Integer objects that contains container lists IDs
395      *         for the sub container lists of the specified container.
396      *
397      * @throws JahiaException occurs if there was an error executing the
398      *                        queries on the database.
399      */
400     public Vector   getContainerSubContainerListIDs (int containerID,
401                                                    EntryLoadRequest loadVersion)
402             throws JahiaException {
403         Connection   dbConn = null;
404         PreparedStatement   stmt = null;
405         ResultSet   rs = null;
406         Vector   theList = null;
407         try {
408             // gets connexion
409             dbConn = ConnectionDispenser.getConnection ();
410 
411             if ( subContainerListIDsByContainerCache != null && loadVersion != null
412                  && loadVersion.getWorkflowState()>EntryLoadRequest.VERSIONED_WORKFLOW_STATE){
413                 theList = (Vector  )this.subContainerListIDsByContainerCache
414                     .get(getSubCtnListIDsByCtnCacheKey(containerID,loadVersion));
415             }
416             if ( theList != null ){
417                 return (Vector  )theList.clone();
418             } else {
419                 theList = new Vector  ();
420             }
421 
422             // Get all container lists IDs.
423             if (loadVersion == null) {
424                 stmt = dbConn.prepareStatement ("SELECT DISTINCT id_jahia_ctn_lists " +
425                         "FROM jahia_ctn_lists " +
426                         "WHERE parententryid_jahia_ctn_lists=? ORDER BY id_jahia_ctn_lists");
427                 stmt.setInt (1, containerID);
428                 rs = stmt.executeQuery ();
429                 while (rs.next ()) {
430                     int ctnListID = rs.getInt ("id_jahia_ctn_lists");
431                     theList.add (new Integer   (ctnListID));
432                 }
433             // if we want to load the staged version
434             } else {
435                 if (loadVersion.isStaging ()) {
436                     stmt =
437                         dbConn.prepareStatement(
438                         "SELECT id_jahia_ctn_lists, version_id FROM jahia_ctn_lists WHERE parententryid_jahia_ctn_lists=? AND workflow_state>0 ORDER BY id_jahia_ctn_lists, workflow_state DESC");
439                     stmt.setInt(1, containerID);
440                     rs = stmt.executeQuery();
441                     int ctnListID, oldCtnListID, versionID;
442 
443                     oldCtnListID = -1;
444                     while (rs.next()) {
445                         ctnListID = rs.getInt("id_jahia_ctn_lists");
446                         versionID = rs.getInt("version_id");
447                         if ( !theList.contains(new Integer  (ctnListID)) ){
448                             // this is a new container, we add it in the list, also it has not
449                             // been deleted (rights!=-1)
450                             if (ctnListID != oldCtnListID) {
451                                 if (loadVersion.isWithMarkedForDeletion()) {
452                                     theList.add(new Integer  (ctnListID));
453                                 }
454                                 else {
455                                     if (versionID != -1) {
456                                         theList.add(new Integer  (ctnListID));
457                                     }
458                                 }
459                             }
460                         }
461                         oldCtnListID = ctnListID;
462                     }
463                 } else if (loadVersion.isVersioned ()) {
464                     // if we want to load the version ID
465                     stmt =
466                             dbConn.prepareStatement (
467                                     "SELECT id_jahia_ctn_lists, version_id FROM jahia_ctn_lists WHERE parententryid_jahia_ctn_lists=? AND workflow_state <=1 AND version_id<=? AND version_id > -1 ORDER BY id_jahia_ctn_lists, version_id DESC");
468                     stmt.setInt (1, containerID);
469                     stmt.setInt (2, loadVersion.getVersionID ());
470                     rs = stmt.executeQuery ();
471                     int ctnListID, oldCtnListID, versionID;
472                     oldCtnListID = -1;
473                     while (rs.next ()) {
474                         ctnListID = rs.getInt ("id_jahia_ctn_lists");
475                         versionID = rs.getInt ("version_id");
476                         if ( !theList.contains(new Integer  (ctnListID)) ){
477                             // this is a new container, we add it in the list, also it has not
478                             // been deleted (verID!=-1)
479                             if ( (ctnListID != oldCtnListID) &&
480                                 (versionID != -1)) {
481                                 if (!theList.contains(new Integer  (ctnListID))) {
482                                     theList.add(new Integer  (ctnListID));
483                                 }
484                             }
485                         }
486                         oldCtnListID = ctnListID;
487                     }
488                 } else {
489                     // we do the normal request
490                     stmt =
491                         dbConn.prepareStatement(
492                         "SELECT DISTINCT id_jahia_ctn_lists FROM jahia_ctn_lists WHERE parententryid_jahia_ctn_lists=? AND workflow_state=1 ORDER BY id_jahia_ctn_lists ASC");
493                     stmt.setInt(1, containerID);
494                     rs = stmt.executeQuery();
495                     while (rs.next()) {
496                         theList.add(new Integer  (rs.getInt("id_jahia_ctn_lists")));
497                     }
498                 }
499                 // store in cache
500                 if ( this.subContainerListIDsByContainerCache != null ){
501                    if ( loadVersion.getWorkflowState()
502                         >EntryLoadRequest.VERSIONED_WORKFLOW_STATE ){
503                        this.subContainerListIDsByContainerCache
504                            .put(getSubCtnListIDsByCtnCacheKey(containerID,loadVersion),theList);
505                    }
506                 }
507             }
508 
509         } catch (SQLException   se) {
510             String   errorMsg = "Error in getPageTopLevelContainerListIDs : " + se.getMessage ();
511             logger.debug (errorMsg + " -> BAILING OUT", se);
512             throw new JahiaException ("Cannot load containers from the database",
513                     errorMsg, JahiaException.DATABASE_ERROR,
514                     JahiaException.CRITICAL_SEVERITY, se);
515         } finally {
516             try {
517 
518                 if (stmt != null) stmt.close ();
519             } catch (SQLException   ex) {
520                 logger.debug ("Cannot free resources", ex);
521             }
522         }
523 
524         return theList;
525     }
526 
527     /**
528      * Retrieves the containerListIDs for container lists that are directly
529      * attached to the page (meaning that they are not container lists
530      * contained with a container) regardless of their workflow state.
531      *
532      * @param pageID the page for which to retrieve top level container list
533      *               IDs
534      *
535      * @return a Set of Integer objects that contain the IDs of the
536      *         top level container lists in a page.
537      *
538      * @throws JahiaException if there was an error while communicating with
539      *                        the database.
540      */
541     public Set   getAllPageTopLevelContainerListIDs (int pageID)
542             throws JahiaException {
543         Connection   dbConn = null;
544         PreparedStatement   stmt = null;
545         ResultSet   rs = null;
546         Set   theSet = new TreeSet   ();
547         try {
548             // gets connexion
549             dbConn = ConnectionDispenser.getConnection ();
550 
551             stmt =
552                     dbConn.prepareStatement (
553                             "SELECT id_jahia_ctn_lists, workflow_state, version_id FROM jahia_ctn_lists WHERE pageid_jahia_ctn_lists=? AND parententryid_jahia_ctn_lists=0 ORDER BY id_jahia_ctn_lists, workflow_state DESC");
554             stmt.setInt (1, pageID);
555             rs = stmt.executeQuery ();
556             int ctnListID;
557 
558             while (rs.next ()) {
559                 ctnListID = rs.getInt ("id_jahia_ctn_lists");
560                 // this is a new container, we add it in the list
561                 if ( !theSet.contains(new Integer  (ctnListID)) ){
562                     theSet.add(new Integer  (ctnListID));
563                 }
564             }
565         } catch (SQLException   se) {
566             String   errorMsg = "Error in getAllPageTopLevelContainerListIDs : " + se.getMessage ();
567             logger.debug (errorMsg + " -> BAILING OUT", se);
568             throw new JahiaException ("Cannot load containers from the database",
569                     errorMsg, JahiaException.DATABASE_ERROR,
570                     JahiaException.CRITICAL_SEVERITY, se);
571         } finally {
572             try {
573 
574                 if (stmt != null) stmt.close ();
575             } catch (SQLException   ex) {
576                 logger.debug ("Cannot free resources", ex);
577             }
578         }
579 
580         return theSet;
581     }
582 
583 
584     /**
585      * Retrieves the containerListIDs for container lists that are directly
586      * attached to the page (meaning that they are not container lists
587      * contained with a container) regardless of their workflow state.
588      *
589      * @param pageID      the page for which to retrieve top level container list
590      *                    IDs
591      * @param loadRequest specifies for which workflowState and version_id to
592      *                    load the container IDs.
593      *
594      * @return a Set of Integer objects that contain the IDs of the
595      *         top level container lists in a page.
596      *
597      * @throws JahiaException if there was an error while communicating with
598      *                        the database.
599      */
600     public SortedSet   getAllPageTopLevelContainerListIDs (int pageID,
601                                                          EntryLoadRequest loadRequest)
602             throws JahiaException {
603         Connection   dbConn = null;
604         PreparedStatement   stmt = null;
605         ResultSet   rs = null;
606         SortedSet   theSet = new TreeSet   ();
607         try {
608             // gets connexion
609             dbConn = ConnectionDispenser.getConnection ();
610             String   sqlQuery = null;
611 
612             if (loadRequest == null) {
613                 sqlQuery =
614                         "SELECT DISTINCT id_jahia_ctn_lists " +
615                         "FROM jahia_ctn_lists WHERE " +
616                         "pageid_jahia_ctn_lists=?" +
617                         " AND parententryid_jahia_ctn_lists=0 " +
618                         " ORDER BY id_jahia_ctn_lists";
619                 stmt = dbConn.prepareStatement (sqlQuery);
620                 stmt.setInt(1, pageID);                        
621                 rs = stmt.executeQuery ();
622                 int ctnListID;
623                 while (rs.next ()) {
624                     ctnListID = rs.getInt ("id_jahia_ctn_lists");
625                     theSet.add (new Integer   (ctnListID));
626                 }
627             } else if (loadRequest.isCurrent ()) {
628                 sqlQuery =
629                         "SELECT DISTINCT id_jahia_ctn_lists, workflow_state, version_id " +
630                         "FROM jahia_ctn_lists WHERE " +
631                         "pageid_jahia_ctn_lists=?" +
632                         " AND parententryid_jahia_ctn_lists=0 " +
633                         " AND workflow_state=1" +
634                         " ORDER BY id_jahia_ctn_lists, version_id DESC";
635                 stmt = dbConn.prepareStatement (sqlQuery);
636                 stmt.setInt(1, pageID);                        
637                 rs = stmt.executeQuery ();
638                 int ctnListID;
639                 while (rs.next ()) {
640                     ctnListID = rs.getInt ("id_jahia_ctn_lists");
641                     if ( !theSet.contains(new Integer  (ctnListID)) ){
642                         theSet.add(new Integer  (ctnListID));
643                     }
644                 }
645             } else if (loadRequest.isStaging ()) {
646                 sqlQuery =
647                         "SELECT id_jahia_ctn_lists, workflow_state, version_id " +
648                         "FROM jahia_ctn_lists WHERE " +
649                         "pageid_jahia_ctn_lists=?" +
650                         " AND parententryid_jahia_ctn_lists=0 " +
651                         " AND workflow_state>=1" +
652                         " ORDER BY id_jahia_ctn_lists, version_id DESC";
653                 stmt = dbConn.prepareStatement (sqlQuery);
654                 stmt.setInt(1, pageID);                                           
655                 rs = stmt.executeQuery ();
656                 int ctnListID;
657                 while (rs.next ()) {
658                     ctnListID = rs.getInt ("id_jahia_ctn_lists");
659                     if ( !theSet.contains(new Integer  (ctnListID)) ){
660                         theSet.add(new Integer  (ctnListID));
661                     }
662                 }
663             } else if (loadRequest.isVersioned ()) {
664                 sqlQuery =
665                         "SELECT id_jahia_ctn_lists " +
666                         "FROM jahia_ctn_lists WHERE " +
667                         "pageid_jahia_ctn_lists=?" +
668                         " AND parententryid_jahia_ctn_lists=0 " +
669                         " AND workflow_state<=1" +
670                         " AND version_id<=?" +
671                         " ORDER BY id_jahia_ctn_lists, version_id DESC";
672                 stmt = dbConn.prepareStatement (sqlQuery);
673                 stmt.setInt(1, pageID);        
674                 stmt.setInt(2, loadRequest.getVersionID());                                   
675                 rs = stmt.executeQuery ();
676                 int oldCtnListID = -1;
677                 while (rs.next ()) {
678                     int ctnListID = rs.getInt ("id_jahia_ctn_lists");
679                     if ( !theSet.contains(new Integer  (ctnListID)) ){
680                         if ( (oldCtnListID == -1) || (oldCtnListID != ctnListID)) {
681                             // we only add the first version we find
682                             theSet.add(new Integer  (ctnListID));
683                         }
684                     }
685                     oldCtnListID = ctnListID;
686                 }
687             }
688         } catch (SQLException   se) {
689             String   errorMsg = "Error in getAllPageTopLevelContainerListIDs : " + se.getMessage ();
690             logger.debug (errorMsg + " -> BAILING OUT", se);
691             throw new JahiaException ("Cannot load containers from the database",
692                     errorMsg, JahiaException.DATABASE_ERROR,
693                     JahiaException.CRITICAL_SEVERITY, se);
694         } finally {
695             try {
696 
697                 if (stmt != null) stmt.close ();
698             } catch (SQLException   ex) {
699                 logger.debug ("Cannot free resources", ex);
700             }
701         }
702 
703         return theSet;
704     }
705 
706     /**
707      * loads all the container list ids by their page id and definition id
708      *
709      * @param pageID the page ID
710      * @param defID  the container definition id
711      *
712      * @return a Vector of container list IDs
713      *
714      * @throws raises a JahiaException if a data access error occurs
715      * @throws raises a JahiaException if cannot free resources
716      */
717     public Vector   db_get_container_list_ids (int pageID, int defID,
718                                              EntryLoadRequest loadVersion)
719             throws JahiaException {
720         Connection   dbConn = null;
721         Statement   stmt = null;
722         ResultSet   rs = null;
723         Vector   theList = new Vector   ();
724         try {
725             // gets connexion
726             dbConn = ConnectionDispenser.getConnection ();
727             stmt = dbConn.createStatement ();
728             String   sqlQuery = null;
729 
730             // if we want to load the staged version
731             if (loadVersion.isStaging ()) {
732                 sqlQuery = new String   ("SELECT id_jahia_ctn_lists,version_id "
733                         + "FROM jahia_ctn_lists "
734                         + "WHERE pageid_jahia_ctn_lists=" + pageID + " "
735                         + "AND ctndefid_jahia_ctn_lists=" + defID + " AND workflow_state>0 "
736                         + "ORDER BY id_jahia_ctn_lists ASC, workflow_state DESC");
737 //               logger.debug ( "Loading staged version... ");
738                 rs = stmt.executeQuery (sqlQuery);
739                 int ctnID;
740                 int oldCtnID = -1;
741                 int versionID;
742                 while (rs.next ()) {
743                     ctnID = rs.getInt ("id_jahia_ctn_lists");
744                     versionID = rs.getInt ("version_id");
745                     if ( !theList.contains(new Integer  (ctnID)) ){
746                         // this is a new container, we add it in the list, also it has not
747                         // been deleted (rights!=-1)
748                         if ( (ctnID != oldCtnID)) {
749                             if (loadVersion.isWithMarkedForDeletion()) {
750                                 theList.add(new Integer  (ctnID));
751                             }
752                             else {
753                                 if (versionID != -1) {
754                                     theList.add(new Integer  (ctnID));
755                                 }
756                             }
757                         }
758                     }
759                     oldCtnID = ctnID;
760                 }
761             } else if (loadVersion.isVersioned ()) { // if we want to load the version ID
762                 sqlQuery = new String   ("SELECT id_jahia_ctn_lists,version_id "
763                         + "FROM jahia_ctn_lists "
764                         + "WHERE pageid_jahia_ctn_lists=" + pageID + " "
765                         + "AND ctndefid_jahia_ctn_lists=" + defID + " AND workflow_state<=1 "
766                         + "AND version_id<=" + loadVersion.getVersionID () + " AND version_id > -1 "
767                         + "ORDER BY id_jahia_ctn_lists ASC, version_id DESC");
768 //               logger.debug("Loading staged version...");
769                 rs = stmt.executeQuery (sqlQuery);
770                 int ctnID;
771                 int oldCtnID = -1;
772                 int versionID;
773                 while (rs.next ()) {
774                     ctnID = rs.getInt ("id_jahia_ctn_lists");
775                     versionID = rs.getInt ("version_id");
776                     if ( !theList.contains(new Integer  (ctnID)) ){
777                         // this is a new container, we add it in the list, also it has not
778                         // been deleted (verID!=-1)
779                         if ( (ctnID != oldCtnID) && (versionID != -1)) {
780                             theList.add(new Integer  (ctnID));
781                         }
782                     }
783                     oldCtnID = ctnID;
784                 }
785             } else { // we do the normal request
786                 sqlQuery = new String   ("SELECT DISTINCT id_jahia_ctn_lists FROM jahia_ctn_lists "
787                         + "WHERE pageid_jahia_ctn_lists=" + pageID + " "
788                         + "AND ctndefid_jahia_ctn_lists=" + defID + " AND workflow_state=1 "
789                         + " ORDER BY id_jahia_ctn_lists ASC");
790                 rs = stmt.executeQuery (sqlQuery);
791                 while (rs.next ()) {
792                     int ctnID = rs.getInt ("id_jahia_ctn_lists");
793                     if ( !theList.contains(new Integer  (ctnID)) ){
794                         theList.add(new Integer  (ctnID));
795                     }
796                 }
797             }
798         } catch (SQLException   se) {
799             String   errorMsg = "Error in db_get_container_list_ids : " + se.getMessage ();
800             logger.debug (errorMsg + " -> BAILING OUT", se);
801             throw new JahiaException ("Cannot load containers from the database",
802                     errorMsg, JahiaException.DATABASE_ERROR,
803                     JahiaException.CRITICAL_SEVERITY, se);
804         } finally {
805             try {
806 
807                 if (stmt != null) stmt.close ();
808             } catch (SQLException   ex) {
809                 logger.debug ("Cannot free resources", ex);
810             }
811         }
812 
813         return theList;
814     } // db_get_container_list_ids
815 
816 
817     /***
818      * FIXME NK the method is the original version of db_get_container_list_ids.
819      * loads all the container list ids by their page id and definition id
820      *
821      * @param        pageID          the page ID
822      * @param        defID           the container definition id
823      * @return       a Vector of container list IDs
824      *
825      * @exception    raises a JahiaException if a data access error occurs
826      * @exception    raises a JahiaException if cannot free resources
827      *
828      */
829     /*
830     public Vector db_get_container_list_ids_ori( int pageID, int defID, EntryLoadRequest loadVersion )
831     throws JahiaException
832     {
833         Connection dbConn = null;
834         Statement stmt = null;
835         ResultSet rs = null;
836         ResultSet rs2 = null;
837         Vector theList = new Vector();
838         try {
839             // gets connexion
840             dbConn = ConnectionDispenser.getConnection();
841             stmt = dbConn.createStatement();
842             String sqlQuery = null;
843 
844             // if we want to load the staged version
845             if (loadVersion.isStaging()){
846                sqlQuery = new String("SELECT listid_jahia_ctn_entries,rights_jahia_ctn_entries,workflow_state,version_id "
847                             + "FROM jahia_ctn_entries "
848                             + "WHERE pageid_jahia_ctn_entries=" + pageID + " "
849                             + "AND ctndefid_jahia_ctn_entries=" + defID + " AND workflow_state>0 "
850                             + "ORDER BY listid_jahia_ctn_entries ASC, workflow_state DESC");
851 //               logger.debug ( "Loading staged version... ");
852                rs = stmt.executeQuery( sqlQuery );
853                int ctnID;
854                int oldCtnID =-1;
855                int rights;
856                int versionID;
857                while ( rs.next() ) {
858                    ctnID = rs.getInt("listid_jahia_ctn_entries");
859                    rights = rs.getInt("rights_jahia_ctn_entries");
860                    versionID = rs.getInt("version_id");
861                    // this is a new container, we add it in the list, also it has not
862                    // been deleted (rights!=-1)
863                    if ((ctnID!=oldCtnID)){
864                        if (loadVersion.isWithMarkedForDeletion()) {
865                            theList.add(new Integer(ctnID));
866                        } else {
867                            if (versionID != -1) {
868                                theList.add(new Integer(ctnID));
869                            }
870                        }
871                    }
872                    oldCtnID = ctnID;
873                }
874             } else if (loadVersion.isVersioned()) { // if we want to load the version ID
875                sqlQuery = new String("SELECT listid_jahia_ctn_entries,rights_jahia_ctn_entries,version_id "
876                             + "FROM jahia_ctn_entries "
877                             + "WHERE pageid_jahia_ctn_entries=" + pageID + " "
878                             + "AND ctndefid_jahia_ctn_entries=" + defID + " AND workflow_state<=1 "
879                             + "AND version_id<="+loadVersion.getVersionID()+" AND version_id > -1 "
880                             + "ORDER BY listid_jahia_ctn_entries ASC, version_id DESC");
881 //               logger.debug("Loading staged version...");
882                rs = stmt.executeQuery( sqlQuery );
883                int ctnID;
884                int oldCtnID =-1;
885                int rights;
886                int versionID;
887                while (rs.next()) {
888                    ctnID = rs.getInt("listid_jahia_ctn_entries");
889                    rights = rs.getInt("rights_jahia_ctn_entries");
890                    versionID = rs.getInt("version_id");
891                    // this is a new container, we add it in the list, also it has not
892                    // been deleted (verID!=-1)
893                    if ((ctnID!=oldCtnID) && (versionID!=-1))
894                    {
895                         theList.add( new Integer( ctnID ) );
896                    }
897                    oldCtnID = ctnID;
898                }
899             } else { // we do the normal request
900                 sqlQuery = new String( "SELECT DISTINCT listid_jahia_ctn_entries FROM jahia_ctn_entries "
901                             + "WHERE pageid_jahia_ctn_entries=" + pageID + " "
902                             + "AND ctndefid_jahia_ctn_entries=" + defID + " AND workflow_state=1 "
903                             + " ORDER BY listid_jahia_ctn_entries ASC");
904                 rs = stmt.executeQuery( sqlQuery );
905                 while (rs.next()) {
906                     theList.add( new Integer( rs.getInt( "listid_jahia_ctn_entries" ) ) );
907                 }
908             }
909         } catch (SQLException se) {
910             String errorMsg = "Error in db_get_container_list_ids : " + se.getMessage();
911             logger.debug(errorMsg + " -> BAILING OUT", se);
912             throw new JahiaException(   "Cannot load containers from the database",
913                                         errorMsg, JahiaException.DATABASE_ERROR,
914                                         JahiaException.CRITICAL_SEVERITY, se );
915         } finally {
916             try {
917 
918                 if ( stmt != null ) stmt.close();
919             } catch ( SQLException ex ) {
920                 logger.debug("Cannot free resources", ex);
921             }
922         }
923 
924         return theList;
925     } // db_get_container_list_ids
926     */
927 
928 
929     /**
930      * loads all the container ids of a container list. This list is sorted
931      * by rank.
932      *
933      * @param listID               used only if fromAllContainerList is false
934      * @param loadVersion
935      * @param fromAllContainerList
936      *
937      * @return a Vector of container IDs
938      *
939      * @throws raises a JahiaException if a data access error occurs
940      * @throws raises a JahiaException if cannot free resources
941      */
942     public Vector   db_get_container_ids_in_container_list (int listID, EntryLoadRequest loadVersion,
943                                                           boolean fromAllContainerLists)
944             throws JahiaException {
945         Connection   dbConn = null;
946         PreparedStatement   stmt = null;
947         ResultSet   rs = null;
948         Vector   theList = null;
949         Vector   tempRank = new Vector   ();
950         try {
951             dbConn = ConnectionDispenser.getConnection ();
952 
953             if ( containerIDsByContainerListCache != null && !fromAllContainerLists ){
954                 theList = (Vector  )this.containerIDsByContainerListCache
955                     .get(getCtnIDsByCtnListCacheKey(listID,loadVersion));
956             }
957             if ( theList != null ){
958                 return (Vector  )theList.clone();
959             } else {
960                 theList = new Vector  ();
961             }
962 
963 
964             // Get all containers IDs.
965             if (loadVersion == null) {
966                 if (fromAllContainerLists) {
967                     stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries");
968                 } else {
969                     stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries WHERE listid_jahia_ctn_entries=?");
970                     stmt.setInt(1, listID);
971                 }
972                 rs = stmt.executeQuery ();
973                 while (rs.next ()) {
974                     int ctnID = rs.getInt (1);
975                     theList.add (new Integer   (ctnID));
976                 }
977                 rs.close();
978             } else
979             // is it a staging load ?
980                 if (loadVersion.isStaging ()) {
981                     if (fromAllContainerLists) {
982                         stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries, version_id, workflow_state FROM jahia_ctn_entries WHERE workflow_state>=1 ORDER BY id_jahia_ctn_entries ASC, workflow_state DESC");
983                     } else {
984                         stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries, version_id, workflow_state FROM jahia_ctn_entries WHERE listid_jahia_ctn_entries=? AND workflow_state>=1 ORDER BY id_jahia_ctn_entries ASC, workflow_state DESC");
985                         stmt.setInt(1, listID);
986                     }
987                     rs = stmt.executeQuery ();
988                     int previousCtnID, ctnID, rank, versionID;
989                     previousCtnID = -2;
990                     // rank sort not very optimized, but this is only when we're in staging....
991                     while (rs.next ()) {
992                         ctnID = rs.getInt (1);
993                         rank = rs.getInt (2);
994                         versionID = rs.getInt (3);
995 
996                         if ( !theList.contains(new Integer  (ctnID)) ){
997                             if (previousCtnID != ctnID) {
998                                 if (loadVersion.isWithMarkedForDeletion()) {
999                                     if (rank != 0) {
1000                                        int index = 0;
1001                                        while ( (index < tempRank.size()) &&
1002                                               ( ( (Integer  ) tempRank.elementAt(
1003                                            index)).intValue() < rank)) {
1004                                            index++;
1005                                        }
1006                                        theList.insertElementAt(new Integer  (ctnID),
1007                                            index);
1008                                        tempRank.insertElementAt(new Integer  (rank),
1009                                            index);
1010                                    }
1011                                    else {
1012                                        // if the ranking is at zero we add it at the end
1013                                        // of the list.
1014                                        theList.add(new Integer  (ctnID));
1015                                    }
1016                                }
1017                                else {
1018                                    if (versionID != -1) {
1019                                        if (rank != 0) {
1020                                            int index = 0;
1021                                            while ( (index < tempRank.size()) &&
1022                                                   ( ( (Integer  ) tempRank.elementAt(
1023                                                index)).intValue() < rank)) {
1024                                                index++;
1025                                            }
1026                                            theList.insertElementAt(new Integer  (
1027                                                ctnID), index);
1028                                            tempRank.insertElementAt(new Integer  (
1029                                                rank), index);
1030                                        }
1031                                        else {
1032                                            // if the ranking is at zero we add it at the end
1033                                            // of the list.
1034                                            theList.add(new Integer  (ctnID));
1035                                        }
1036                                    }
1037                                }
1038                            }
1039                        }
1040                        previousCtnID = ctnID;
1041                    }
1042                    rs.close();
1043                } else
1044                // is it a version ID ?
1045                    if (loadVersion.isVersioned ()) {
1046                        if (fromAllContainerLists) {
1047                            stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries, version_id FROM jahia_ctn_entries WHERE workflow_state<=1 AND version_id<=? ORDER BY id_jahia_ctn_entries ASC, version_id DESC");
1048                            stmt.setInt(1, loadVersion.getVersionID());
1049                        } else {
1050                            stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries, version_id FROM jahia_ctn_entries WHERE listid_jahia_ctn_entries=? AND workflow_state<=1 AND version_id<=? ORDER BY id_jahia_ctn_entries ASC, version_id DESC");
1051                            stmt.setInt(1, listID);
1052                            stmt.setInt(2, loadVersion.getVersionID());
1053                        }
1054                        rs = stmt.executeQuery ();
1055                        int previousCtnID, ctnID, rank, versionID;
1056                        previousCtnID = -2;
1057                        // rank sort not very optimized, but this is only when we're in versionID mode....
1058                        while (rs.next ()) {
1059                            ctnID = rs.getInt (1);
1060                            rank = rs.getInt (2);
1061                            versionID = rs.getInt (3);
1062                            if ( !theList.contains(new Integer  (ctnID)) ){
1063                                if ( (previousCtnID != ctnID) && (versionID != -1)) {
1064                                    if (rank != 0) {
1065                                        int index = 0;
1066                                        while ( (index < tempRank.size()) &&
1067                                               ( ( (Integer  ) tempRank.elementAt(index)).
1068                                                intValue() < rank)) {
1069                                            index++;
1070                                        }
1071                                        theList.insertElementAt(new Integer  (ctnID),
1072                                            index);
1073                                        tempRank.insertElementAt(new Integer  (rank),
1074                                            index);
1075                                    }
1076                                    else {
1077                                        // if the ranking is at zero we add it at the end
1078                                        // of the list.
1079                                        theList.add(new Integer  (ctnID));
1080                                    }
1081                                }
1082                            }
1083                            previousCtnID = ctnID;
1084                        }
1085                        rs.close();
1086                    }
1087                    // normal case
1088                    else {
1089                        if (fromAllContainerLists) {
1090                            stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries FROM jahia_ctn_entries WHERE workflow_state=1 ORDER BY rank_jahia_ctn_entries, id_jahia_ctn_entries ASC");
1091                        } else {
1092                            stmt = dbConn.prepareStatement("SELECT id_jahia_ctn_entries, rank_jahia_ctn_entries FROM jahia_ctn_entries WHERE listid_jahia_ctn_entries=? AND workflow_state=1 ORDER BY rank_jahia_ctn_entries, id_jahia_ctn_entries ASC");
1093                            stmt.setInt(1, listID);
1094                        }
1095                        rs = stmt.executeQuery ();
1096                        while (rs.next ()) {
1097                            theList.add (new Integer   (rs.getInt (1)));
1098                        }
1099                        rs.close();
1100                    }
1101        } catch (SQLException   se) {
1102            String   errorMsg = "Error in db_get_container_ids_in_container_list : " + se.getMessage ();
1103            logger.debug (errorMsg + " -> BAILING OUT", se);
1104            throw new JahiaException ("Cannot load containers from the database",
1105                    errorMsg, JahiaException.DATABASE_ERROR,
1106                    JahiaException.CRITICAL_SEVERITY, se);
1107        } finally {
1108            try {
1109
1110                if (stmt != null) stmt.close ();
1111            } catch (SQLException   ex) {
1112                logger.debug ("Cannot free resources", ex);
1113            }
1114        }
1115        if ( theList == null ){
1116            return null;
1117        }
1118        if ( loadVersion == null ||
1119             loadVersion.getWorkflowState()>=EntryLoadRequest.ACTIVE_WORKFLOW_STATE ){
1120            this.containerIDsByContainerListCache.put(getCtnIDsByCtnListCacheKey(listID,loadVersion),theList);
1121        }
1122
1123        return (Vector  )theList.clone();
1124    } // db_get_container_ids_in_container_list
1125
1126    /**
1127     * Retrieves all the container IDs in a list, regardless of workflow state.
1128     * This is useful notably to purge all the containers in a list.
1129     *
1130     * @param listID identifier for the list
1131     *
1132     * @return a Set of Integer object containing container IDs still active,
1133     *         staged, versioned or deleted.
1134     *
1135     * @throws JahiaException occurs if there was a problem communicating with
1136     *                        the database.
1137     */
1138    public Set   getAllContainerIDsInList (int listID)
1139            throws JahiaException {
1140        Connection   dbConn = null;
1141        PreparedStatement   stmt = null;
1142        ResultSet   rs = null;
1143        Set   resultSet = new TreeSet   ();
1144        try {
1145            dbConn = ConnectionDispenser.getConnection ();
1146            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries, rank_jahia_ctn_entries FROM jahia_ctn_entries "
1147                + "WHERE listid_jahia_ctn_entries=?"
1148                + " ORDER BY rank_jahia_ctn_entries, id_jahia_ctn_entries ASC";
1149            stmt = dbConn.prepareStatement (sqlQuery);
1150            stmt.setInt(1, listID);
1151            rs = stmt.executeQuery ();
1152            while (rs.next ()) {
1153                resultSet.add (new Integer   (rs.getInt ("id_jahia_ctn_entries")));
1154            }
1155
1156        } catch (SQLException   se) {
1157            String   errorMsg = "Error in getAllContainerIDsInList : " + se.getMessage ();
1158            logger.debug (errorMsg + " -> BAILING OUT", se);
1159            throw new JahiaException ("Cannot load containers from the database",
1160                    errorMsg, JahiaException.DATABASE_ERROR,
1161                    JahiaException.CRITICAL_SEVERITY, se);
1162        } finally {
1163            try {
1164
1165                if (stmt != null) stmt.close ();
1166            } catch (SQLException   ex) {
1167                logger.debug ("Cannot free resources", ex);
1168            }
1169        }
1170
1171        return resultSet;
1172
1173    }
1174
1175    public Hashtable   db_load_all_active_fields_from_container_from_page (int pageID)
1176            throws JahiaException {
1177        Hashtable   result = new Hashtable   ();
1178        Connection   dbConn = null;
1179        PreparedStatement   stmt = null;
1180        ResultSet   rs = null;
1181        try {
1182            String   sqlQuery = "SELECT * FROM jahia_fields_data ";
1183            sqlQuery += "WHERE pageid_jahia_fields_data=? ";
1184            sqlQuery +=
1185                    "ORDER BY ctnid_jahia_fields_data, rank_jahia_fields_data, id_jahia_fields_data ASC";
1186
1187            dbConn = ConnectionDispenser.getConnection ();
1188            stmt = dbConn.prepareStatement (sqlQuery);
1189            stmt.setInt(1, pageID);
1190            rs = stmt.executeQuery ();
1191
1192            while (rs.next ()) {
1193                int ctnid = rs.getInt ("ctnid_jahia_fields_data");
1194                Vector   theList = (Vector  ) result.get (new Integer   (ctnid));
1195                if (theList == null) {
1196                    theList = new Vector   ();
1197                    result.put (new Integer   (ctnid), theList);
1198                }
1199                theList.add (new Integer   (rs.getInt ("id_jahia_fields_data")));
1200            }
1201        } catch (SQLException   se) {
1202            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
1203            logger.debug (errorMsg + " -> BAILING OUT", se);
1204            throw new JahiaException ("Cannot load containers from the database",
1205                    errorMsg, JahiaException.DATABASE_ERROR,
1206                    JahiaException.CRITICAL_SEVERITY, se);
1207        } finally {
1208            try {
1209
1210                if (stmt != null) stmt.close ();
1211            } catch (SQLException   ex) {
1212                logger.debug ("Cannot free resources", ex);
1213            }
1214        }
1215
1216        return result;
1217    }
1218
1219    /**
1220     * loads all the container ids of a container list. This list is sorted
1221     * on a given field name.
1222     *
1223     * @param listID  the container list ID
1224     * @param String  fieldName    the fieldname on which to filter
1225     * @param boolean asc         Asc. or desc. ordering ( true = asc )
1226     *
1227     * @return a Vector of container IDs
1228     *
1229     * @throws raises a JahiaException if a data access error occurs
1230     * @throws raises a JahiaException if cannot free resources
1231     * @author NK
1232     */
1233    public Vector   db_get_container_ids_in_container_list (int listID, String   fieldName,
1234                                                          boolean asc,
1235                                                          EntryLoadRequest loadVersion)
1236            throws JahiaException {
1237        Connection   dbConn = null;
1238        PreparedStatement   stmt = null;
1239        ResultSet   rs = null;
1240        Vector   theList = new Vector   ();
1241        try {
1242            StringBuffer   buff = new StringBuffer   ();
1243            buff.append (
1244                    "SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c WHERE listid_jahia_ctn_entries=?");
1245            buff.append (
1246                    " AND ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def=?)");            
1247            if (!loadVersion.isStaging ()) {
1248                buff.append ("AND workflow_state=1 ");
1249            }
1250            buff.append ("ORDER BY value_jahia_fields_data ");
1251            if (!asc) {
1252                buff.append (" DESC ");
1253            }
1254
1255            //logger.debug(" query : " + buff.toString() );
1256
1257            dbConn = ConnectionDispenser.getConnection ();
1258            stmt = dbConn.prepareStatement (buff.toString());
1259            stmt.setInt(1, listID);
1260            stmt.setString(2, JahiaTools.quote (fieldName));
1261            rs = stmt.executeQuery ();
1262
1263            while (rs.next ()) {
1264                int id = rs.getInt ("id_jahia_ctn_entries");
1265                //logger.debug( " added id : " + id );
1266                //logger.debug( " added val : " + val );
1267                theList.add (new Integer   (id));
1268            }
1269        } catch (SQLException   se) {
1270            String   errorMsg = "Error in db_get_container_ids_in_container_list : " + se.getMessage ();
1271            logger.debug (errorMsg + " -> BAILING OUT", se);
1272            throw new JahiaException ("Cannot load containers from the database",
1273                    errorMsg, JahiaException.DATABASE_ERROR,
1274                    JahiaException.CRITICAL_SEVERITY, se);
1275        } finally {
1276            try {
1277
1278                if (stmt != null)
1279                    stmt.close ();
1280            } catch (SQLException   ex) {
1281                logger.debug ("Cannot free resources", ex);
1282            }
1283        }
1284
1285        return theList;
1286    } // db_get_container_ids_in_container_list
1287
1288
1289    /**
1290     * loads all the fields ids of a specific container
1291     *
1292     * @param contID the container ID
1293     *
1294     * @return a Vector of field IDs
1295     *
1296     * @throws raises a JahiaException if a data access error occurs
1297     * @throws raises a JahiaException if cannot free resources
1298     */
1299    public Vector   db_get_field_ids_in_container (int contID, EntryLoadRequest loadVersion)
1300            throws JahiaException {
1301        Connection   dbConn = null;
1302        PreparedStatement   stmt = null;
1303        ResultSet   rs = null;
1304        Vector   theList = new Vector   ();
1305        Vector   tempRank = new Vector   ();
1306        try {
1307            dbConn = ConnectionDispenser.getConnection ();
1308
1309            // Get all container's field IDs.
1310            if (loadVersion == null) {
1311                stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_fields_data, rank_jahia_fields_data FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? ORDER BY rank_jahia_fields_data, id_jahia_fields_data ASC");
1312                stmt.setInt(1, contID);
1313                rs = stmt.executeQuery ();
1314                while (rs.next ()) {
1315                    int fieldID = rs.getInt ("id_jahia_fields_data");
1316                    theList.add (new Integer   (fieldID));
1317                }
1318                rs.close();
1319            } else
1320            // staging version
1321                if (loadVersion.isStaging ()) {
1322                    stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_fields_data,rank_jahia_fields_data,version_id,workflow_state FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state>=1 ORDER BY id_jahia_fields_data ASC, workflow_state DESC");
1323                    stmt.setInt(1, contID);
1324                    rs = stmt.executeQuery ();
1325                    int ofID, fID, rank, versionID;
1326                    ofID = -2;
1327                    // rank sort not very optimized, but this is only when we're in versionID mode....
1328                    while (rs.next ()) {
1329                        fID = rs.getInt ("id_jahia_fields_data");
1330                        rank = rs.getInt ("rank_jahia_fields_data");
1331                        versionID = rs.getInt ("version_id");
1332                        if (ofID != fID) {
1333                            if (loadVersion.isWithMarkedForDeletion ()) {
1334                                int index = 0;
1335                                while ((index < tempRank.size ()) && (((Integer  ) tempRank.elementAt (
1336                                        index)).intValue () < rank)) {
1337                                    index++;
1338                                }
1339                                theList.insertElementAt (new Integer   (fID), index);
1340                                tempRank.insertElementAt (new Integer   (rank), index);
1341                            } else {
1342                                if (versionID != -1) {
1343                                    int index = 0;
1344                                    while ((index < tempRank.size ()) && (((Integer  ) tempRank.elementAt (
1345                                            index)).intValue () < rank)) {
1346                                        index++;
1347                                    }
1348                                    theList.insertElementAt (new Integer   (fID), index);
1349                                    tempRank.insertElementAt (new Integer   (rank), index);
1350                                }
1351                            }
1352                        }
1353                        ofID = fID;
1354                    }
1355                    rs.close();
1356                } else
1357                // versionID
1358                    if (loadVersion.isVersioned ()) {
1359                        stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_fields_data,rank_jahia_fields_data,version_id FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state<=1 AND version_id<=? ORDER BY id_jahia_fields_data ASC, version_id DESC");
1360                        stmt.setInt(1, contID);
1361                        stmt.setInt(2, loadVersion.getVersionID());
1362                        rs = stmt.executeQuery ();
1363                        int ofID, fID, rank, versionID;
1364                        ofID = -2;
1365                        // rank sort not very optimized, but this is only when we're in versionID mode....
1366                        while (rs.next ()) {
1367                            fID = rs.getInt ("id_jahia_fields_data");
1368                            rank = rs.getInt ("rank_jahia_fields_data");
1369                            versionID = rs.getInt ("version_id");
1370                            if ((ofID != fID) && (versionID != -1)) {
1371                                int index = 0;
1372                                while ((index < tempRank.size ()) && (((Integer  ) tempRank.elementAt (
1373                                        index)).intValue () < rank)) {
1374                                    index++;
1375                                }
1376                                theList.insertElementAt (new Integer   (fID), index);
1377                                tempRank.insertElementAt (new Integer   (rank), index);
1378                            }
1379                            ofID = fID;
1380                        }
1381                        rs.close();
1382                    }
1383                    // normal query
1384                    else {
1385                        stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_fields_data,rank_jahia_fields_data FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state=1 ORDER BY rank_jahia_fields_data, id_jahia_fields_data ASC");
1386                        stmt.setInt(1, contID);
1387                        rs = stmt.executeQuery ();
1388                        while (rs.next ()) {
1389                            theList.add (new Integer   (rs.getInt ("id_jahia_fields_data")));
1390                        }
1391                        rs.close();
1392                    }
1393
1394        } catch (SQLException   se) {
1395            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
1396            logger.debug (errorMsg + " -> BAILING OUT", se);
1397            throw new JahiaException ("Cannot load containers from the database",
1398                    errorMsg, JahiaException.DATABASE_ERROR,
1399                    JahiaException.CRITICAL_SEVERITY, se);
1400        } finally {
1401            try {
1402
1403                if (stmt != null) stmt.close ();
1404            } catch (SQLException   ex) {
1405                logger.debug ("Cannot free resources", ex);
1406            }
1407        }
1408
1409        return theList;
1410    } // db_get_field_ids_in_container
1411
1412    /**
1413     * loads all the fields ids of a specific container
1414     *
1415     * @param contID the container ID
1416     *
1417     * @return a Vector of field IDs
1418     *
1419     * @throws raises a JahiaException if a data access error occurs or cannot free resources
1420     */
1421    public Map   db_get_field_ids_and_defs_in_container (int contID, EntryLoadRequest loadVersion)
1422            throws JahiaException {
1423        Connection   dbConn = null;
1424        PreparedStatement   stmt = null;
1425        ResultSet   rs = null;
1426        Map   theList = new HashMap  ();
1427        try {
1428            dbConn = ConnectionDispenser.getConnection ();
1429
1430            // Get all container's field IDs.
1431            if (loadVersion == null) {
1432                stmt = dbConn.prepareStatement("SELECT DISTINCT id_jahia_fields_data, rank_jahia_fields_data, fielddefid_jahia_fields_data FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? ORDER BY rank_jahia_fields_data, id_jahia_fields_data ASC");
1433                stmt.setInt(1, contID);
1434                rs = stmt.executeQuery ();
1435                while (rs.next ()) {
1436                    int fieldID = rs.getInt ("id_jahia_fields_data");
1437                    int fieldDefID = rs.getInt ("fielddefid_jahia_fields_data");                    
1438                    theList.put (new Integer   (fieldDefID), new Integer   (fieldID));
1439                }
1440                rs.close();
1441            } else
1442            // staging version
1443                if (loadVersion.isStaging ()) {
1444                    stmt = dbConn.prepareStatement("SELECT id_jahia_fields_data,rank_jahia_fields_data,version_id,workflow_state, fielddefid_jahia_fields_data  FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state>=1 ORDER BY id_jahia_fields_data ASC, workflow_state DESC");
1445                    stmt.setInt(1, contID);
1446                    rs = stmt.executeQuery ();
1447                    int ofID, fID, fDefID, versionID;
1448                    ofID = -2;
1449                    // rank sort not very optimized, but this is only when we're in versionID mode....
1450                    while (rs.next ()) {
1451                        fID = rs.getInt ("id_jahia_fields_data");
1452                        fDefID = rs.getInt ("fielddefid_jahia_fields_data");                        
1453                        versionID = rs.getInt ("version_id");
1454                        if (ofID != fID) {
1455                            if (loadVersion.isWithMarkedForDeletion () || versionID != -1) {
1456                                theList.put (new Integer   (fDefID), new Integer   (fID));                                
1457                            }
1458                        }
1459                        ofID = fID;
1460                    }
1461                    rs.close();
1462                } else
1463                // versionID
1464                    if (loadVersion.isVersioned ()) {
1465                        stmt = dbConn.prepareStatement("SELECT id_jahia_fields_data,rank_jahia_fields_data,version_id, fielddefid_jahia_fields_data FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state<=1 AND version_id<=? ORDER BY id_jahia_fields_data ASC, version_id DESC");
1466                        stmt.setInt(1, contID);
1467                        stmt.setInt(2, loadVersion.getVersionID());
1468                        rs = stmt.executeQuery ();
1469                        int ofID, fID, fDefID, versionID;
1470                        ofID = -2;
1471                        // rank sort not very optimized, but this is only when we're in versionID mode....
1472                        while (rs.next ()) {
1473                            fID = rs.getInt ("id_jahia_fields_data");
1474                            fDefID = rs.getInt ("fielddefid_jahia_fields_data");                            
1475                            versionID = rs.getInt ("version_id");
1476                            if ((ofID != fID) && (versionID != -1)) {
1477                                theList.put (new Integer   (fDefID), new Integer   (fID));
1478                            }
1479                            ofID = fID;
1480                        }
1481                        rs.close();
1482                    }
1483                    // normal query
1484                    else {
1485                        stmt = dbConn.prepareStatement("SELECT id_jahia_fields_data,rank_jahia_fields_data, fielddefid_jahia_fields_data FROM jahia_fields_data WHERE ctnid_jahia_fields_data=? AND workflow_state=1 ORDER BY rank_jahia_fields_data, id_jahia_fields_data ASC");
1486                        stmt.setInt(1, contID);
1487                        rs = stmt.executeQuery ();
1488                        while (rs.next ()) {
1489                            theList.put (new Integer   (rs.getInt ("fielddefid_jahia_fields_data")), new Integer   (rs.getInt ("id_jahia_fields_data")));                            
1490                        }
1491                        rs.close();
1492                    }
1493
1494        } catch (SQLException   se) {
1495            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
1496            logger.debug (errorMsg + " -> BAILING OUT", se);
1497            throw new JahiaException ("Cannot load containers from the database",
1498                    errorMsg, JahiaException.DATABASE_ERROR,
1499                    JahiaException.CRITICAL_SEVERITY, se);
1500        } finally {
1501            try {
1502
1503                if (stmt != null) stmt.close ();
1504            } catch (SQLException   ex) {
1505                logger.debug ("Cannot free resources", ex);
1506            }
1507        }
1508
1509        return theList;
1510    } // db_get_field_ids_and_defs_in_container
1511    
1512    
1513    /**
1514     * Returns all the field IDs in a container, regardless of workflow state.
1515     * This is useful notably for purging operations
1516     *
1517     * @param containerID identifier of the container for which to retrieve
1518     *                    the field IDs
1519     *
1520     * @return a Set of Integer objects containing the field IDs in the
1521     *         container regardless of state.
1522     *
1523     * @throws JahiaException thrown in case the communication with the database
1524     *                        or the execution of request fails.
1525     */
1526    public Set   getAllFieldIDsInContainer (int containerID)
1527            throws JahiaException {
1528
1529        Set   result = new TreeSet   ();
1530        Vector   v = ContentContainerTools.getInstance()
1531            .getFieldIDsByContainer(containerID,null,false);
1532        result.addAll(v);
1533        return result;
1534    }
1535
1536
1537    /**
1538     * loads all containers definitions ids
1539     *
1540     * @return a Vector of container definition ids
1541     *
1542     * @throws throws a critical JahiaException if a data access occurs
1543     * @throws throws a warning JahiaException if cannot free resources
1544     * @see org.jahia.data.containers.JahiaContainerDefinition
1545     */
1546    public Vector   db_get_all_container_definition_ids ()
1547            throws JahiaException {
1548        Connection   dbConn = null;
1549        PreparedStatement   stmt = null;
1550        ResultSet   rs = null;
1551        Vector   theList = new Vector   ();
1552        try {
1553            String   sqlQuery = "SELECT id_jahia_ctn_def FROM jahia_ctn_def";
1554
1555            dbConn = ConnectionDispenser.getConnection ();
1556            stmt = dbConn.prepareStatement (sqlQuery);
1557            rs = stmt.executeQuery ();
1558
1559            while (rs.next ()) {
1560                theList.add (new Integer   (rs.getInt ("id_jahia_ctn_def")));
1561            }
1562
1563        } catch (SQLException   se) {
1564            String   errorMsg = "Error in db_get_all_container_definition_ids : " + se.getMessage () + " -> BAILING OUT";
1565            logger.debug (errorMsg, se);
1566            throw new JahiaException ("Cannot load definitions from the database",
1567                    errorMsg, JahiaException.DATABASE_ERROR,
1568                    JahiaException.CRITICAL_SEVERITY, se);
1569        } finally {
1570            try {
1571
1572                if (stmt != null) stmt.close ();
1573            } catch (SQLException   ex) {
1574                logger.debug ("Cannot free resources", ex);
1575            }
1576        }
1577        return theList;
1578    } // end db_get_all_container_definition_ids
1579
1580
1581    /**
1582     * gets only the container IDs that are in staging (ACTIVE>1) for the current page
1583     *
1584     * @param pageID the page ID
1585     *
1586     * @return a Vector of container ids
1587     *
1588     * @throws throws a critical JahiaException if SQL error
1589     * @throws throws a warning JahiaException if cannot free resources
1590     */
1591    public Vector   db_get_only_staged_container_ids_in_page (int pageID)
1592            throws JahiaException {
1593        Connection   dbConn = null;
1594        PreparedStatement   stmt = null;
1595        ResultSet   rs = null;
1596        Vector   ctnIDs = new Vector   ();
1597        try {
1598            dbConn = ConnectionDispenser.getConnection ();
1599
1600            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries " +
1601                    "WHERE pageid_jahia_ctn_entries=? " + 
1602                    "AND (workflow_state>1)" +
1603                    "ORDER BY id_jahia_ctn_entries ASC";
1604
1605            stmt = dbConn.prepareStatement (sqlQuery);
1606            stmt.setInt(1, pageID);
1607            rs = stmt.executeQuery ();
1608
1609            while (rs.next ()) {
1610                ctnIDs.add (new Integer   (rs.getInt ("id_jahia_ctn_entries")));
1611            }
1612        } catch (SQLException   se) {
1613            String   errorMsg = "Error in db_get_only_staged_container_ids_in_page : " + se.getMessage ();
1614            logger.debug (errorMsg + " -> BAILING OUT", se);
1615            throw new JahiaException ("Cannot load containers from the database",
1616                    errorMsg, JahiaException.DATABASE_ERROR,
1617                    JahiaException.CRITICAL_SEVERITY, se);
1618        } finally {
1619            try {
1620
1621                if (stmt != null) stmt.close ();
1622            } catch (SQLException   ex) {
1623                logger.debug ("Cannot free resources", ex);
1624            }
1625        }
1626        return ctnIDs;
1627    }
1628
1629    /**
1630     * gets only the container list IDs that are in staging (ACTIVE>1) for the current page
1631     *
1632     * @param pageID the page ID
1633     *
1634     * @return a Vector of container list ids
1635     *
1636     * @throws throws a critical JahiaException if SQL error
1637     * @throws throws a warning JahiaException if cannot free resources
1638     */
1639    public Vector   db_get_only_staged_container_list_ids_in_page (int pageID)
1640            throws JahiaException {
1641        Connection   dbConn = null;
1642        PreparedStatement   stmt = null;
1643        ResultSet   rs = null;
1644        Vector   listIDs = new Vector   ();
1645        try {
1646            dbConn = ConnectionDispenser.getConnection ();
1647            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_lists FROM jahia_ctn_lists "
1648                    + "WHERE pageid_jahia_ctn_lists=? AND (workflow_state>1) ORDER BY id_jahia_ctn_lists ASC";            
1649            stmt = dbConn.prepareStatement (sqlQuery);
1650            stmt.setInt(1, pageID);
1651
1652            rs = stmt.executeQuery ();
1653
1654            while (rs.next ()) {
1655                listIDs.add (new Integer   (rs.getInt ("id_jahia_ctn_lists")));
1656            }
1657        } catch (SQLException   se) {
1658            String   errorMsg = "Error in db_get_only_staged_container_list_ids_in_page : " + se.getMessage ();
1659            logger.debug (errorMsg + " -> BAILING OUT", se);
1660            throw new JahiaException ("Cannot load containerlists from the database",
1661                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.CRITICAL_SEVERITY);
1662        } finally {
1663            try {
1664
1665                if (stmt != null) stmt.close ();
1666            } catch (SQLException   ex) {
1667                logger.debug ("Cannot free resources", ex);
1668            }
1669        }
1670        return listIDs;
1671    }
1672
1673
1674    /***
1675     * gets all containers id
1676     *
1677     * @return       a Vector of ids
1678     *
1679     * @exception   throws a critical JahiaException if SQL error
1680     * @exception   throws a warning JahiaException if cannot free resources
1681     *
1682     */
1683/*    public Vector db_get_all_containers_id(JahiaLoadVersion loadVersion)
1684    throws JahiaException
1685    {
1686        Vector result = new Vector();
1687        Connection dbConn = null;
1688        Statement stmt = null;
1689        ResultSet rs = null;
1690        JahiaField theField = null;
1691        Integer nb_to_insert;
1692        try {
1693            String sqlQuery = "SELECT id_jahia_ctn_entries FROM jahia_ctn_entries";
1694
1695            dbConn = ConnectionDispenser.getConnection();
1696            stmt = dbConn.createStatement();
1697            rs = stmt.executeQuery(sqlQuery );
1698
1699            while (rs.next())
1700            {
1701                nb_to_insert = new Integer (rs.getInt("id_jahia_ctn_entries"));
1702                result.addElement (nb_to_insert);
1703            }
1704
1705        } catch (SQLException se) {
1706            String errorMsg = "Error in db_get_all_containers_id : " + se.getMessage();
1707            logger.debug(errorMsg + " -> BAILING OUT", se);
1708        } finally {
1709            try {
1710
1711                if ( stmt != null ) stmt.close();
1712            } catch ( SQLException ex )
1713                logger.debug("Cannot free resources", ex);
1714            }
1715        }
1716
1717        return result;
1718    } // end db_get_all_containers_id*/
1719
1720
1721    /**
1722     * gets all containers id for a given site
1723     *
1724     * @param int siteID
1725     *
1726     * @return a Vector of ids
1727     *
1728     * @throws throws a critical JahiaException if SQL error
1729     * @throws throws a warning JahiaException if cannot free resources
1730     */
1731    public Vector   db_get_all_containers_id (int siteID)
1732            throws JahiaException {
1733        Vector   result = new Vector   ();
1734        Connection   dbConn = null;
1735        PreparedStatement   stmt = null;
1736        ResultSet   rs = null;
1737        Integer   nb_to_insert;
1738        try {
1739            dbConn = ConnectionDispenser.getConnection ();
1740            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries WHERE jahiaid_jahia_ctn_entries =?";            
1741            stmt = dbConn.prepareStatement (sqlQuery);
1742            stmt.setInt(1, siteID);
1743            rs = stmt.executeQuery ();
1744
1745            while (rs.next ()) {
1746                nb_to_insert = new Integer   (rs.getInt ("id_jahia_ctn_entries"));
1747                result.add (nb_to_insert);
1748            }
1749
1750        } catch (SQLException   se) {
1751            String   errorMsg = "Error in db_get_all_containers_id : " + se.getMessage ();
1752            logger.debug (errorMsg + " -> BAILING OUT", se);
1753            throw new JahiaException ("Cannot load containers from the database",
1754                    errorMsg, JahiaException.DATABASE_ERROR,
1755                    JahiaException.ERROR_SEVERITY, se);
1756        } finally {
1757            try {
1758
1759                if (stmt != null) stmt.close ();
1760            } catch (SQLException   ex) {
1761                logger.debug ("Cannot free resources", ex);
1762            }
1763        }
1764
1765        return result;
1766    } // end db_get_all_containers_id
1767
1768    /**
1769     * gets all containers in the system
1770     *
1771     * @return a Vector of ids
1772     *
1773     * @throws throws a critical JahiaException if SQL error
1774     * @throws throws a warning JahiaException if cannot free resources
1775     */
1776    public Vector   db_get_all_containers_id ()
1777            throws JahiaException {
1778        return db_get_all_containers_id(false);
1779    }
1780
1781    /**
1782     * gets all containers in the system
1783     *
1784     * @param orderByRanking if true orderby ranking
1785     * @return a Vector of ids
1786     *
1787     * @throws throws a critical JahiaException if SQL error
1788     * @throws throws a warning JahiaException if cannot free resources
1789     */
1790    public Vector   db_get_all_containers_id (boolean orderByRanking)
1791            throws JahiaException {
1792        Vector   result = new Vector   ();
1793        Connection   dbConn = null;
1794        PreparedStatement   stmt = null;
1795        ResultSet   rs = null;
1796        Integer   nb_to_insert;
1797        try {
1798            String   sqlQuery = "";
1799            if ( orderByRanking ){
1800                sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries, rank_jahia_ctn_entries FROM jahia_ctn_entries ORDER BY rank_jahia_ctn_entries";
1801            } else {
1802                sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries";                
1803            }
1804
1805            dbConn = ConnectionDispenser.getConnection ();
1806            stmt = dbConn.prepareStatement (sqlQuery);
1807            rs = stmt.executeQuery ();
1808
1809            while (rs.next ()) {
1810                nb_to_insert = new Integer   (rs.getInt ("id_jahia_ctn_entries"));
1811                result.add (nb_to_insert);
1812            }
1813
1814        } catch (SQLException   se) {
1815            String   errorMsg = "Error in db_get_all_containers_id : " + se.getMessage ();
1816            logger.debug (errorMsg + " -> BAILING OUT", se);
1817            throw new JahiaException ("Cannot load containers from the database",
1818                    errorMsg, JahiaException.DATABASE_ERROR,
1819                    JahiaException.ERROR_SEVERITY, se);
1820        } finally {
1821            try {
1822
1823                if (stmt != null) stmt.close ();
1824            } catch (SQLException   ex) {
1825                logger.debug ("Cannot free resources", ex);
1826            }
1827        }
1828
1829        return result;
1830    }
1831    
1832    /**
1833     * loads all the container list ids in a container.
1834     *
1835     * @param ctnID the container ID
1836     *
1837     * @return a Vector of container list IDs
1838     *
1839     * @throws raises a JahiaException if a data access error occurs
1840     * @throws raises a JahiaException if cannot free resources
1841     */
1842    public Vector   db_get_containerlist_ids_in_container (int ctnID)
1843            throws JahiaException {
1844
1845        logger.debug ("ctnID=" + ctnID);
1846
1847        Connection   dbConn = null;
1848        PreparedStatement   stmt = null;
1849        ResultSet   rs = null;
1850        Vector   theList = new Vector   ();
1851        try {
1852            String   sqlQuery = 
1853                    "SELECT DISTINCT id_jahia_ctn_lists FROM jahia_ctn_lists WHERE parententryid_jahia_ctn_lists=?";
1854
1855            dbConn = ConnectionDispenser.getConnection ();
1856            stmt = dbConn.prepareStatement (sqlQuery);
1857            stmt.setInt(1, ctnID);
1858            rs = stmt.executeQuery ();
1859
1860            while (rs.next ()) {
1861                theList.add (new Integer   (rs.getInt ("id_jahia_ctn_lists")));
1862            }
1863        } catch (SQLException   se) {
1864            String   errorMsg = "Error in db_get_containerlist_ids_in_container : " + se.getMessage ();
1865            logger.error (errorMsg + " -> BAILING OUT");
1866            throw new JahiaException ("Cannot load container lists from the database",
1867                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.DATABASE_ERROR);
1868        } finally {
1869            try {
1870
1871                if (stmt != null) stmt.close ();
1872            } catch (SQLException   ex) {
1873                logger.debug ("Cannot free resources");
1874            }
1875        }
1876
1877        return theList;
1878    }
1879
1880    public Hashtable   db_load_all_fields_from_container_from_page (int pageID)
1881            throws JahiaException {
1882        Hashtable   result = new Hashtable   ();
1883        Connection   dbConn = null;
1884        PreparedStatement   stmt = null;
1885        ResultSet   rs = null;
1886        try {
1887
1888            dbConn = ConnectionDispenser.getConnection ();
1889            stmt = dbConn.prepareStatement("SELECT ctnid_jahia_fields_data, id_jahia_fields_data FROM jahia_fields_data WHERE pageid_jahia_fields_data=? ORDER BY ctnid_jahia_fields_data, rank_jahia_fields_data, id_jahia_fields_data ASC");
1890            stmt.setInt(1, pageID);
1891            rs = stmt.executeQuery ();
1892
1893            while (rs.next ()) {
1894                int ctnid = rs.getInt (1);
1895                Vector   theList = (Vector  ) result.get (new Integer   (ctnid));
1896                if (theList == null) {
1897                    theList = new Vector   ();
1898                    result.put (new Integer   (ctnid), theList);
1899                }
1900                Integer   fieldID = new Integer   (rs.getInt (2));
1901                if (!theList.contains (fieldID)) {
1902                    theList.add (fieldID);
1903                }
1904            }
1905            rs.close();
1906        } catch (SQLException   se) {
1907            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
1908            logger.error (errorMsg + " -> BAILING OUT");
1909            throw new JahiaException ("Cannot load containers from the database",
1910                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.DATABASE_ERROR);
1911        } finally {
1912            try {
1913
1914                if (stmt != null) stmt.close ();
1915            } catch (SQLException   ex) {
1916                logger.debug ("Cannot free resources");
1917            }
1918        }
1919
1920        return result;
1921    }
1922
1923    public Hashtable   db_load_all_containers_from_containerlist_from_page (int pageID,
1924                                                                          String   fieldName,
1925                                                                          boolean asc)
1926            throws JahiaException {
1927        Hashtable   result = new Hashtable   ();
1928        Connection   dbConn = null;
1929        PreparedStatement   stmt = null;
1930        ResultSet   rs = null;
1931        try {
1932
1933            StringBuffer   buff = new StringBuffer   (
1934                    "SELECT DISTINCT id_jahia_ctn_entries, listid_jahia_ctn_entries, pageid_jahia_ctn_lists FROM jahia_ctn_entries a, jahia_fields_data b, jahia_fields_def c WHERE AND pageid_jahia_ctn_lists=?");
1935            buff.append (
1936                    " AND ( a.id_jahia_ctn_entries = b.ctnid_jahia_fields_data AND b.fielddefid_jahia_fields_data = c.id_jahia_fields_def AND c.name_jahia_fields_def=?");
1937            buff.append (") ORDER BY value_jahia_fields_data ");
1938            if (!asc) {
1939                buff.append (" DESC ");
1940            }
1941
1942            dbConn = ConnectionDispenser.getConnection ();
1943            stmt = dbConn.prepareStatement (buff.toString ());
1944            stmt.setInt(1, pageID);
1945            stmt.setString(2, JahiaTools.quote (fieldName));
1946            rs = stmt.executeQuery ();
1947
1948            while (rs.next ()) {
1949                int ctnid = rs.getInt ("id_jahia_ctn_entries");
1950                int listID = rs.getInt ("listid_jahia_ctn_entries");
1951                Vector   theList = (Vector  ) result.get (new Integer   (listID));
1952                if (theList == null) {
1953                    theList = new Vector   ();
1954                    result.put (new Integer   (listID), theList);
1955                }
1956                theList.add (new Integer   (ctnid));
1957            }
1958        } catch (SQLException   se) {
1959            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
1960            logger.error (errorMsg + " -> BAILING OUT");
1961            throw new JahiaException ("Cannot load containers from the database",
1962                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.DATABASE_ERROR);
1963        } finally {
1964            try {
1965
1966                if (stmt != null) stmt.close ();
1967            } catch (SQLException   ex) {
1968                logger.debug ("Cannot free resources");
1969            }
1970        }
1971
1972        return result;
1973    }
1974
1975    public Hashtable   db_load_all_containers_from_containerlist_from_page (int pageID)
1976            throws JahiaException {
1977        Hashtable   result = new Hashtable   ();
1978        Connection   dbConn = null;
1979        PreparedStatement   stmt = null;
1980        ResultSet   rs = null;
1981        try {
1982
1983            String   sqlQuery = 
1984                    "SELECT DISTINCT id_jahia_ctn_entries, listid_jahia_ctn_entries FROM jahia_ctn_entries WHERE pageid_jahia_ctn_entries=?";
1985            
1986            dbConn = ConnectionDispenser.getConnection ();
1987            stmt = dbConn.prepareStatement (sqlQuery);
1988            stmt.setInt(1, pageID);
1989            rs = stmt.executeQuery ();
1990
1991            while (rs.next ()) {
1992                int ctnid = rs.getInt ("id_jahia_ctn_entries");
1993                int listID = rs.getInt ("listid_jahia_ctn_entries");
1994                Vector   theList = (Vector  ) result.get (new Integer   (listID));
1995                if (theList == null) {
1996                    theList = new Vector   ();
1997                    result.put (new Integer   (listID), theList);
1998                }
1999                theList.add (new Integer   (ctnid));
2000            }
2001        } catch (SQLException   se) {
2002            String   errorMsg = "Error in db_get_field_ids_in_container : " + se.getMessage ();
2003            logger.error (errorMsg + " -> BAILING OUT");
2004            throw new JahiaException ("Cannot load containers from the database",
2005                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.DATABASE_ERROR);
2006        } finally {
2007            try {
2008
2009                if (stmt != null) stmt.close ();
2010            } catch (SQLException   ex) {
2011                logger.debug ("Cannot free resources");
2012            }
2013        }
2014
2015        return result;
2016    }
2017
2018    public Hashtable   db_load_all_containerlists_from_container_from_page (int pageID)
2019            throws JahiaException {
2020        Hashtable   result = new Hashtable   ();
2021        Connection   dbConn = null;
2022        PreparedStatement   stmt = null;
2023        ResultSet   rs = null;
2024        try {
2025
2026            String   sqlQuery = 
2027                    "SELECT DISTINCT id_jahia_ctn_lists, parententryid_jahia_ctn_lists FROM jahia_ctn_lists WHERE pageid_jahia_ctn_lists=?";
2028            
2029            dbConn = ConnectionDispenser.getConnection ();
2030            stmt = dbConn.prepareStatement (sqlQuery);
2031            stmt.setInt(1, pageID);
2032            rs = stmt.executeQuery ();
2033            
2034            while (rs.next ()) {
2035                int ctnListid = rs.getInt ("id_jahia_ctn_lists");
2036                int parentid = rs.getInt ("parententryid_jahia_ctn_lists");
2037                Vector   theList = (Vector  ) result.get (new Integer   (parentid));
2038                if (theList == null) {
2039                    theList = new Vector   ();
2040                    result.put (new Integer   (parentid), theList);
2041                }
2042                theList.add (new Integer   (ctnListid));
2043            }
2044        } catch (SQLException   se) {
2045            String   errorMsg = "Error in db_load_all_containerlists_from_container_from_page : " + se.getMessage ();
2046            logger.error (errorMsg + " -> BAILING OUT");
2047            throw new JahiaException ("Cannot load containers from the database",
2048                    errorMsg, JahiaException.DATABASE_ERROR, JahiaException.DATABASE_ERROR);
2049        } finally {
2050            try {
2051
2052                if (stmt != null) stmt.close ();
2053            } catch (SQLException   ex) {
2054                logger.debug ("Cannot free resources");
2055            }
2056        }
2057        return result;
2058    }
2059
2060
2061    /**
2062     * return a Map of ACL ids for all containers of a given container list
2063     * The key is the ctn id and the value is the acl id.
2064     *
2065     * @param int cListID
2066     *
2067     * @return Hashtable acls
2068     *
2069     * @throws throws a critical JahiaException if SQL error
2070     * @throws throws a warning JahiaException if cannot free resources
2071     */
2072    public Hashtable   db_get_all_containers_aclid (int cListID)
2073            throws JahiaException {
2074        Hashtable   acls = new Hashtable   ();
2075        Connection   dbConn = null;
2076        PreparedStatement   stmt = null;
2077        ResultSet   rs = null;
2078        try {
2079            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries,rights_jahia_ctn_entries FROM jahia_ctn_entries WHERE listid_jahia_ctn_entries =?";
2080
2081            dbConn = ConnectionDispenser.getConnection ();
2082            stmt = dbConn.prepareStatement (sqlQuery);
2083            stmt.setInt(1, cListID);
2084            rs = stmt.executeQuery ();
2085
2086            Integer   ctnID = null;
2087            Integer   aclID = null;
2088            while (rs.next ()) {
2089                ctnID = new Integer   (rs.getInt ("id_jahia_ctn_entries"));
2090                aclID = new Integer   (rs.getInt ("rights_jahia_ctn_entries"));
2091                acls.put (ctnID, aclID);
2092            }
2093
2094        } catch (SQLException   se) {
2095            String   errorMsg = "Error in db_get_all_containers_id : " + se.getMessage ();
2096            logger.debug (errorMsg + " -> BAILING OUT", se);
2097            throw new JahiaException ("Cannot load containers from the database",
2098                    errorMsg, JahiaException.DATABASE_ERROR,
2099                    JahiaException.ERROR_SEVERITY, se);
2100        } finally {
2101            try {
2102
2103                if (stmt != null) stmt.close ();
2104            } catch (SQLException   ex) {
2105                logger.debug ("Cannot free resources", ex);
2106            }
2107        }
2108
2109        return acls;
2110    } // end db_get_all_containers_id
2111
2112    /**
2113     * return a Map of ACL ids for all containers of a given list ID
2114     * If listID = -1 , return result from all lists
2115     *
2116     * @param listID the identifer of the list from which to retrieve the
2117     * container ACLID's
2118     *
2119     * @return a Map container Integer object as keys that contain the
2120     * container ID, and as a value a Integer object containing the ACL ID.
2121     *
2122     * @throws JahiaException
2123     */
2124    public Hashtable   getContainerACLIDsByListID (int listID)
2125        throws JahiaException {
2126        Hashtable   acls = new Hashtable  ();
2127        Connection   dbConn = null;
2128        PreparedStatement   stmt = null;
2129        ResultSet   rs = null;
2130        try {
2131            String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries,rights_jahia_ctn_entries FROM jahia_ctn_entries ";
2132            if (listID != -1) {
2133                sqlQuery += " WHERE listid_jahia_ctn_entries =?";
2134            }
2135            dbConn = ConnectionDispenser.getConnection();
2136            stmt = dbConn.prepareStatement(sqlQuery);
2137            stmt.setInt(1, listID);
2138            rs = stmt.executeQuery();
2139
2140            Integer   ctnID = null;
2141            Integer   aclID = null;
2142            while (rs.next()) {
2143                ctnID = new Integer  (rs.getInt("id_jahia_ctn_entries"));
2144                aclID = new Integer  (rs.getInt("rights_jahia_ctn_entries"));
2145                acls.put(ctnID, aclID);
2146            }
2147
2148        } catch (SQLException   se) {
2149            String   errorMsg = "Error in getContainerACLIDsByListID : " +
2150                              se.getMessage();
2151            logger.debug(errorMsg + " -> BAILING OUT", se);
2152            throw new JahiaException("Cannot load containers from the database",
2153                                     errorMsg, JahiaException.DATABASE_ERROR,
2154                                     JahiaException.ERROR_SEVERITY, se);
2155        } finally {
2156            try {
2157
2158                if (stmt != null)
2159                    stmt.close();
2160            } catch (SQLException   ex) {
2161                logger.debug("Cannot free resources", ex);
2162            }
2163        }
2164
2165        return acls;
2166    }
2167
2168    /**
2169     * Returns the ACL ID for a container ID
2170     * @param containerID
2171     * @return the ACL ID if the container was found, or -1 if not found.
2172     * @throws JahiaException
2173     */
2174    public int getContainerACLID (int containerID)
2175        throws JahiaException {
2176        // get through cache
2177        ContentContainer container = ContentContainerTools
2178            .getInstance().getContainer(containerID);
2179        if ( container == null ){
2180            return -1;
2181        }
2182        return container.getAclID();
2183        /*
2184        try {
2185            String sqlQuery =
2186                "SELECT rights_jahia_ctn_entries FROM jahia_ctn_entries WHERE id_jahia_ctn_entries=?";
2187            dbConn = ConnectionDispenser.
2188                     getConnection();
2189            stmt = dbConn.prepareStatement(sqlQuery);
2190            stmt.setInt(1, containerID);
2191            rs = stmt.executeQuery();
2192
2193            if (rs.next()) {
2194                aclID = rs.getInt("rights_jahia_ctn_entries");
2195            }
2196
2197        } catch (SQLException se) {
2198            String errorMsg = "Error in getContainerACLID : " +
2199                              se.getMessage();
2200            logger.debug(errorMsg + " -> BAILING OUT", se);
2201            throw new JahiaException("Cannot load containers from the database",
2202                                     errorMsg, JahiaException.DATABASE_ERROR,
2203                                     JahiaException.ERROR_SEVERITY, se);
2204        } finally {
2205            try {
2206
2207                if (stmt != null) {
2208                    stmt.close();
2209                }
2210            } catch (SQLException ex) {
2211                logger.debug("Cannot free resources", ex);
2212            }
2213        }
2214        return aclID;*/
2215    }
2216
2217    /**
2218     * gets all acl for a site
2219     * Used for site extraction
2220     *
2221     * @return a Vector of ids
2222     *
2223     * @author NK
2224     */
2225    public Vector   db_get_all_acls_id (int siteID)
2226            throws JahiaException {
2227        Vector   result = new Vector   ();
2228        Connection   dbConn = null;
2229        PreparedStatement   stmt = null;
2230        ResultSet   rs = null;
2231        Integer   nb_to_insert;
2232        try {
2233            String   sqlQuery = "SELECT DISTINCT rights_jahia_ctn_entries FROM jahia_ctn_entries "
2234                    + " WHERE jahiaid_jahia_ctn_entries=";
2235
2236            dbConn = ConnectionDispenser.getConnection ();
2237            stmt = dbConn.prepareStatement (sqlQuery);
2238            stmt.setInt(1, siteID);
2239            rs = stmt.executeQuery ();
2240
2241            while (rs.next ()) {
2242                nb_to_insert = new Integer   (rs.getInt ("rights_jahia_ctn_entries"));
2243                result.addElement (nb_to_insert);
2244            }
2245
2246        } catch (SQLException   se) {
2247            String   errorMsg = "Error in db_get_all_acls_id(int siteID) : " + se.getMessage ();
2248            logger.debug (errorMsg, se);
2249            throw new JahiaException ("Cannot load containers from the database",
2250                    errorMsg, JahiaException.DATABASE_ERROR,
2251                    JahiaException.ERROR_SEVERITY, se);
2252        } finally {
2253            try {
2254
2255                if (stmt != null) stmt.close ();
2256            } catch (SQLException   ex) {
2257                logger.debug ("error freeing db connection", ex);
2258            }
2259        }
2260
2261
2262        try {
2263            String   sqlQuery = "SELECT DISTINCT jahia_ctn_lists.rights_jahia_ctn_lists FROM jahia_ctn_lists,jahia_ctn_def "
2264                    + "WHERE jahia_ctn_lists.ctndefid_jahia_ctn_lists ="
2265                    + "jahia_ctn_def.id_jahia_ctn_def AND jahia_ctn_def.jahiaid_jahia_ctn_def=?";
2266
2267            dbConn = ConnectionDispenser.getConnection ();
2268            stmt = dbConn.prepareStatement (sqlQuery);
2269            stmt.setInt(1, siteID);            
2270            rs = stmt.executeQuery ();
2271
2272            while (rs.next ()) {
2273                nb_to_insert = new Integer   (rs.getInt ("rights_jahia_ctn_lists"));
2274                result.addElement (nb_to_insert);
2275            }
2276
2277        } catch (SQLException   se) {
2278            String   errorMsg = "Error in db_get_all_acls_id(int siteID) : " + se.getMessage ();
2279            logger.debug (errorMsg, se);
2280            throw new JahiaException ("Cannot load containers from the database",
2281                    errorMsg, JahiaException.DATABASE_ERROR,
2282                    JahiaException.ERROR_SEVERITY, se);
2283        } finally {
2284            try {
2285
2286                if (stmt != null) stmt.close ();
2287            } catch (SQLException   ex) {
2288                logger.debug ("error freeing db connection", ex);
2289            }
2290        }
2291
2292        return result;
2293    } // end db_get_all_containers_id
2294
2295    /**
2296     * Returns a set of subContainerDefinitionIDs that contain the container
2297     * definition ID we are looking for.
2298     *
2299     * @param containerDefinitionID an integer specifying the container
2300     *                              definition for which to find the parent sub definitions.
2301     *
2302     * @return a SortedSet containing Integer that are subContainerDefinitionIDs
2303     *         that contain the container definition we are looking for.
2304     */
2305    public SortedSet   getContainerDefinitionParents (int containerDefinitionID)
2306            throws JahiaException {
2307        SortedSet   resultSet = new TreeSet   ();
2308        Connection   dbConn = null;
2309        PreparedStatement   stmt = null;
2310        ResultSet   rs = null;
2311        try {
2312            dbConn = ConnectionDispenser.getConnection ();
2313            
2314            String   sqlQuery = "SELECT ctnsubdefid_jahia_ctn_struct FROM jahia_ctn_struct "
2315              + "WHERE objtype_jahia_ctn_struct=? AND objdefid_jahia_ctn_struct=?"
2316              + " ORDER BY ctnsubdefid_jahia_ctn_struct ASC";            
2317            
2318            stmt = dbConn.prepareStatement (sqlQuery);
2319            stmt.setInt(1, 2);
2320            stmt.setInt(2, containerDefinitionID);            
2321            rs = stmt.executeQuery ();            
2322            while (rs.next ()) {
2323                resultSet.add (new Integer   (rs.getInt ("ctnsubdefid_jahia_ctn_struct")));
2324            }
2325
2326        } catch (SQLException   se) {
2327            String   errorMsg = "Error in getContainerDefinitionParents : " + se.getMessage ();
2328            logger.debug (errorMsg + " -> BAILING OUT", se);
2329            throw new JahiaException ("Cannot load containers from the database",
2330                    errorMsg, JahiaException.DATABASE_ERROR,
2331                    JahiaException.CRITICAL_SEVERITY, se);
2332        } finally {
2333            try {
2334
2335                if (stmt != null) stmt.close ();
2336            } catch (SQLException   ex) {
2337                logger.debug ("Cannot free resources", ex);
2338            }
2339        }
2340        return resultSet;
2341    }
2342
2343    /**
2344     * Retrieves the containerListIDs for container lists for in a whole site
2345     * that conform to a certain definition ID
2346     *
2347     * @param definitionID the definition ID that is the selection criteria
2348     *                     for the container lists to retrieve
2349     * @param loadRequest  specifies for which workflowState and version_id to
2350     *                     load the container IDs.
2351     *
2352     * @return a Set of Integer objects that contain the IDs of the
2353     *         top level container lists that conform to the definition ID.
2354     *
2355     * @throws JahiaException if there was an error while communicating with
2356     *                        the database.
2357     */
2358    public SortedSet   getTopLevelContainerListIDsByDefID (int definitionID,
2359                                                         EntryLoadRequest loadRequest)
2360            throws JahiaException {
2361        Connection   dbConn = null;
2362        PreparedStatement   stmt = null;
2363        ResultSet   rs = null;
2364        SortedSet   theSet = new TreeSet   ();
2365        try {
2366            // gets connexion
2367            dbConn = ConnectionDispenser.getConnection ();
2368            String   sqlQuery = null;
2369
2370            if (loadRequest == null) {
2371                sqlQuery =
2372                        "SELECT DISTINCT id_jahia_ctn_lists " +
2373                        "FROM jahia_ctn_lists WHERE " +
2374                        "ctndefid_jahia_ctn_lists=?" +
2375                        " AND parententryid_jahia_ctn_lists=0 " +
2376                        " ORDER BY id_jahia_ctn_lists";
2377                stmt = dbConn.prepareStatement (sqlQuery);
2378                stmt.setInt(1, definitionID);
2379                rs = stmt.executeQuery ();
2380                int ctnListID;
2381                while (rs.next ()) {
2382                    ctnListID = rs.getInt ("id_jahia_ctn_lists");
2383                    theSet.add (new Integer   (ctnListID));
2384                }
2385            } else if (loadRequest.isCurrent ()) {
2386                sqlQuery =
2387                        "SELECT id_jahia_ctn_lists, workflow_state, version_id " +
2388                        "FROM jahia_ctn_lists WHERE " +
2389                        "ctndefid_jahia_ctn_lists=?" +
2390                        " AND parententryid_jahia_ctn_lists=0 " +
2391                        " AND workflow_state=1" +
2392                        " ORDER BY id_jahia_ctn_lists, version_id DESC";
2393                stmt = dbConn.prepareStatement (sqlQuery);
2394                stmt.setInt(1, definitionID);                
2395                rs = stmt.executeQuery ();
2396                int ctnListID;
2397                while (rs.next ()) {
2398                    ctnListID = rs.getInt ("id_jahia_ctn_lists");
2399                    theSet.add (new Integer   (ctnListID));
2400                }
2401            } else if (loadRequest.isStaging ()) {
2402                sqlQuery =
2403                        "SELECT id_jahia_ctn_lists, workflow_state, version_id " +
2404                        "FROM jahia_ctn_lists WHERE " +
2405                        "ctndefid_jahia_ctn_lists=?" +
2406                        " AND parententryid_jahia_ctn_lists=0 " +
2407                        " AND workflow_state>=1" +
2408                        " ORDER BY id_jahia_ctn_lists, version_id DESC";
2409                stmt = dbConn.prepareStatement (sqlQuery);
2410                stmt.setInt(1, definitionID);                
2411                rs = stmt.executeQuery ();
2412                int ctnListID;
2413                while (rs.next ()) {
2414                    ctnListID = rs.getInt ("id_jahia_ctn_lists");
2415                    if ( !theSet.contains(new Integer   (ctnListID)) ){
2416                        theSet.add(new Integer  (ctnListID));
2417                    }
2418                }
2419            } else if (loadRequest.isVersioned ()) {
2420                sqlQuery =
2421                        "SELECT id_jahia_ctn_lists " +
2422                        "FROM jahia_ctn_lists WHERE " +
2423                        "ctndefid_jahia_ctn_lists=?" +
2424                        " AND parententryid_jahia_ctn_lists=0 " +
2425                        " AND workflow_state<=1" +
2426                        " AND version_id<=?" + 
2427                        " ORDER BY id_jahia_ctn_lists, version_id DESC";
2428                stmt = dbConn.prepareStatement (sqlQuery);
2429                stmt.setInt(1, definitionID); 
2430                stmt.setInt(2, loadRequest.getVersionID());
2431                rs = stmt.executeQuery ();
2432                int oldCtnListID = -1;
2433                while (rs.next ()) {
2434                    int ctnListID = rs.getInt ("id_jahia_ctn_lists");
2435                    if ( !theSet.contains(new Integer   (ctnListID)) ){
2436                        if ( (oldCtnListID == -1) || (oldCtnListID != ctnListID)) {
2437                            // we only add the first version we find
2438                            theSet.add(new Integer  (ctnListID));
2439                        }
2440                    }
2441                    oldCtnListID = ctnListID;
2442                }
2443            }
2444        } catch (SQLException   se) {
2445            String   errorMsg = "Error in getTopLevelContainerListIDsByDefID : " + se.getMessage ();
2446            logger.debug (errorMsg + " -> BAILING OUT", se);
2447            throw new JahiaException ("Cannot load containers from the database",
2448                    errorMsg, JahiaException.DATABASE_ERROR,
2449                    JahiaException.CRITICAL_SEVERITY, se);
2450        } finally {
2451            try {
2452
2453                if (stmt != null) stmt.close ();
2454            } catch (SQLException   ex) {
2455                logger.debug ("Cannot free resources", ex);
2456            }
2457        }
2458
2459        return theSet;
2460    }
2461
2462    /**
2463     * Returns the container IDs that are in staged OR active state.
2464     *
2465     * @param pageID the page ID
2466     *
2467     * @return a Vector of field list ids
2468     *
2469     * @throws throws a critical JahiaException if SQL error
2470     * @throws throws a warning JahiaException if cannot free resources
2471     */
2472    public Vector   getActiveOrStagedContainerIDsInPage (int pageID)
2473        throws JahiaException {
2474        Connection   dbConn = null;
2475        PreparedStatement   stmt = null;
2476        ResultSet   rs = null;
2477        Vector   containerIDs = new Vector   ();
2478        try {
2479            dbConn = ConnectionDispenser.getConnection ();
2480
2481            final String   sqlQuery = "SELECT DISTINCT id_jahia_ctn_entries FROM jahia_ctn_entries " +
2482                    "WHERE pageid_jahia_ctn_entries=? AND (workflow_state>=1) " +
2483                    "ORDER BY id_jahia_ctn_entries ASC";
2484
2485            stmt = dbConn.prepareStatement (sqlQuery);
2486            stmt.setInt (1, pageID);
2487
2488            rs = stmt.executeQuery ();
2489
2490            while (rs.next ()) {
2491                Integer   curContainerID = new Integer   (rs.getInt (1));
2492                if (!containerIDs.contains (curContainerID)) {
2493                    containerIDs.add (curContainerID);
2494                }
2495            }
2496        } catch (SQLException   se) {
2497            String   errorMsg = "Error in getActiveOrStagedContainerIDsInPage : " +
2498                    se.getMessage ();
2499            logger.error (errorMsg + " -> BAILING OUT");
2500            throw new JahiaException ("Cannot load fields from the database",
2501                    errorMsg, JahiaException.DATABASE_ERROR,
2502                    JahiaException.CRITICAL_SEVERITY, se);
2503        } finally {
2504            try {
2505                if (stmt != null)
2506                    stmt.close ();
2507            } catch (SQLException   ex) {
2508                new JahiaException ("Cannot free resources",
2509                        "getActiveOrStagedContainerIDsInPage : cannot free resources",
2510                        JahiaException.DATABASE_ERROR,
2511                        JahiaException.WARNING_SEVERITY, ex);
2512            }
2513        }
2514
2515        return containerIDs;
2516    }
2517
2518    /**
2519     * Invalidate internal cache for the given ContainerID
2520     *
2521     * @param containerID
2522     */
2523    public void invalidateSubCtnListIDsByCtnCache(int containerID) {
2524        if (subContainerListIDsByContainerCache != null) {
2525            synchronized (subContainerListIDsByContainerCache) {
2526                if (CacheFactory.getInstance().isKeyHierarchyEnabled()) {
2527                    subContainerListIDsByContainerCache.remove(String
2528                            .valueOf(containerID));
2529                } else {
2530                    String   ID = String.valueOf(containerID);
2531                    Object  [] keys = subContainerListIDsByContainerCache.keys();
2532                    int length = keys.length;
2533                    for (int i = 0; i < length; i++) {
2534                        String   key = (String  ) keys[i];
2535                        if (key.startsWith(ID)) {
2536                            subContainerListIDsByContainerCache.remove(key);
2537                        }
2538                    }
2539                }
2540            }
2541        }
2542    }
2543
2544    /**
2545     * Invalidate internal cache for the given Container listID
2546     *
2547     * @param listID
2548     */
2549    public void invalidateCtnIdsByCtnListCache(int listID) {
2550        if (containerIDsByContainerListCache != null) {
2551            synchronized (containerIDsByContainerListCache) {
2552                if (CacheFactory.getInstance().isKeyHierarchyEnabled()) {
2553                    containerIDsByContainerListCache.remove(String
2554                            .valueOf(listID));
2555                } else {
2556                    String   ID = String.valueOf(listID);
2557                    Object  [] keys = containerIDsByContainerListCache.keys();
2558                    int length = keys.length;
2559                    for (int i = 0; i < length; i++) {
2560                        String   key = (String  ) keys[i];
2561                        if (key.startsWith(ID)) {
2562                            containerIDsByContainerListCache.remove(key);
2563                        }
2564                    }
2565                }
2566            }
2567        }
2568    }
2569
2570    private Object   getSubCtnListIDsByCtnCacheKey(int containerID,
2571            EntryLoadRequest loadRequest) {
2572        Object   key = null;
2573        if (CacheFactory.getInstance().isKeyHierarchyEnabled()) {
2574            List   keyList = new ArrayList  (3);
2575            keyList.add(String.valueOf(containerID));
2576            if (loadRequest != null) {
2577                keyList.add(Integer.toString(loadRequest.getWorkflowState()));
2578                keyList.add(loadRequest.isWithMarkedForDeletion() ? "yes"
2579                        : "no");
2580            }
2581            key = keyList;
2582        } else {
2583            StringBuffer   buff = new StringBuffer  (String.valueOf(containerID));
2584            buff.append("_");
2585            buff.append(loadRequest.getWorkflowState());
2586            buff.append("_");
2587            buff.append(loadRequest.isWithMarkedForDeletion() ? "yes" : "no");
2588            key = buff.toString();
2589        }
2590        return key;
2591    }
2592
2593    private Object   getCtnIDsByCtnListCacheKey(int listID,
2594            EntryLoadRequest loadRequest) {
2595        Object   key = null;
2596        if (CacheFactory.getInstance().isKeyHierarchyEnabled()) {
2597            List   keyList = new ArrayList  (3);
2598            keyList.add(String.valueOf(listID));
2599            if (loadRequest != null) {
2600                keyList.add(Integer.toString(loadRequest.getWorkflowState()));
2601                keyList.add(loadRequest.isWithMarkedForDeletion() ? "yes"
2602                        : "no");
2603            }
2604            key = keyList;
2605
2606        } else {
2607            StringBuffer   buff = new StringBuffer  (String.valueOf(listID));
2608            if (loadRequest != null) {
2609                buff.append("_");
2610                buff.append(loadRequest.getWorkflowState());
2611                buff.append("_");
2612                buff.append(loadRequest.isWithMarkedForDeletion() ? "yes"
2613                        : "no");
2614            }
2615            key = buff.toString();
2616        }
2617        return key;
2618    }
2619
2620} // end JahiaContainerUtilsDB
2621
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags