001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.hipergate;
034:
035: import java.sql.SQLException;
036: import java.sql.PreparedStatement;
037: import java.sql.ResultSet;
038:
039: import com.knowgate.debug.DebugFile;
040: import com.knowgate.jdc.JDCConnection;
041: import com.knowgate.dataobjs.DB;
042: import com.knowgate.dataobjs.DBBind;
043: import com.knowgate.dataobjs.DBSubset;
044:
045: /**
046: * Singleton manager for Categories Tree
047: * @author Sergio Montoro Ten
048: * @version 2.1
049: */
050: public class Categories {
051:
052: public Categories() {
053: iRootsCount = -1;
054: sRootsNamedTables = DB.k_categories + " c, " + DB.k_cat_labels
055: + " n," + DB.k_cat_root + " r";
056: sRootsNamedFields = "c." + DB.gu_category + ", c."
057: + DB.nm_category + ", " + DBBind.Functions.ISNULL
058: + "(n." + DB.tr_category + ",''), c." + DB.nm_icon
059: + ", c." + DB.nm_icon2;
060: sRootsNamedFilter = "n." + DB.gu_category + "=c."
061: + DB.gu_category + " AND c." + DB.gu_category + "=r."
062: + DB.gu_category + " AND n." + DB.id_language + "=?";
063:
064: sChildNamedTables = DB.v_cat_tree_labels;
065: sChildNamedFields = DB.gu_category + "," + DB.nm_category + ","
066: + DB.tr_category + ", " + DB.nm_icon + ", "
067: + DB.nm_icon2;
068: sChildNamedFilter = DB.gu_parent_cat + "=? AND ("
069: + DB.id_language + "=? OR " + DB.id_language
070: + " IS NULL)";
071: }
072:
073: //----------------------------------------------------------------------------
074:
075: /**
076: * Clear root categories cache.
077: * Root category names are loaded once and then cached into a static variable.
078: * Use this method for forcing reload of categories from database on next call
079: * to getRoots() or getRootsNamed().
080: */
081: public void clearCache() {
082: oRootsLoaded = false;
083: }
084:
085: // ----------------------------------------------------------
086:
087: /**
088: * <p>Expand Category Childs into k_cat_expand table</p>
089: * @param oConn Database Connection
090: * @param sRootCategoryId GUID of Category to expand.
091: * @throws SQLException
092: */
093: public static void expand(JDCConnection oConn,
094: String sRootCategoryId) throws SQLException {
095: Category oRoot = new Category(sRootCategoryId);
096: oRoot.expand(oConn);
097: }
098:
099: //----------------------------------------------------------------------------
100:
101: /**
102: * <p>Get root category for a given Domain</p>
103: * The root Category for a Domain will be the one such that nm_category=nm_domain
104: * @param oConn Database Connection
105: * @param iDomain Domain Numeric Identifier
106: * @return Category GUID or <b>null</b> if root Category for Domain was not found.
107: * @throws SQLException
108: */
109: public Category forDomain(JDCConnection oConn, int iDomain)
110: throws SQLException {
111: PreparedStatement oStmt;
112: ResultSet oRSet;
113: Category oRetVal;
114:
115: if (DebugFile.trace) {
116: DebugFile
117: .writeln("Begin Categories.forDomain([Connection], "
118: + String.valueOf(iDomain) + ")");
119: DebugFile.incIdent();
120: DebugFile.writeln("Connection.prepareStatement(SELECT "
121: + DB.gu_category + " FROM " + DB.k_categories
122: + " WHERE " + DB.nm_category + "=(SELECT "
123: + DB.nm_domain + " FROM " + DB.k_domains
124: + " WHERE " + DB.id_domain + "=?)");
125: }
126:
127: oStmt = oConn.prepareStatement("SELECT " + DB.gu_category
128: + " FROM " + DB.k_categories + " WHERE "
129: + DB.nm_category + "=(SELECT " + DB.nm_domain
130: + " FROM " + DB.k_domains + " WHERE " + DB.id_domain
131: + "=?)", ResultSet.TYPE_FORWARD_ONLY,
132: ResultSet.CONCUR_READ_ONLY);
133: oStmt.setInt(1, iDomain);
134:
135: oRSet = oStmt.executeQuery();
136:
137: if (oRSet.next())
138: oRetVal = new Category(oConn, oRSet.getString(1));
139: else
140: oRetVal = null;
141:
142: oRSet.close();
143: oStmt.close();
144:
145: if (DebugFile.trace) {
146: DebugFile.decIdent();
147: DebugFile.writeln("End Categories.forDomain() : "
148: + (oRetVal == null ? "null" : "[Category]"));
149: }
150:
151: return oRetVal;
152: } // forDomain()
153:
154: //----------------------------------------------------------------------------
155:
156: /**
157: * <p>Get root categories as a DBSubset.</p>
158: * Root categories are those present at k_cat_root table.<br>
159: * It is recommended to use this criteria instead of seeking those categories
160: * not present as childs at k_cat_tree. Selecting from k_cat_root is much faster
161: * than scanning the k_cat_tree table.
162: * @param oConn Database Connection
163: * @return A single column DBSubset containing th GUID of root categories.
164: * @throws SQLException
165: */
166: public DBSubset getRoots(JDCConnection oConn) throws SQLException {
167:
168: if (DebugFile.trace) {
169: DebugFile
170: .writeln("Begin Categories.getRoots([Connection])");
171: DebugFile.incIdent();
172: }
173:
174: oRoots = new DBSubset(DB.k_cat_root, DB.gu_category, "", 10);
175: iRootsCount = oRoots.load(oConn);
176:
177: if (DebugFile.trace) {
178: DebugFile.decIdent();
179: DebugFile.writeln("End Categories.getRoots()");
180: }
181:
182: return oRoots;
183: } // getRoots
184:
185: //----------------------------------------------------------------------------
186:
187: /**
188: * Get root categories count.
189: * @throws IllegalStateException If getRoots() or getRootsNamed() have not
190: * been called prior to getRootsCount()
191: */
192: public int getRootsCount() throws IllegalStateException {
193: if (-1 == iRootsCount)
194: throw new IllegalStateException(
195: "Must call getRoots() or getRootsNamed() prior to getRootsCount()");
196:
197: return iRootsCount;
198: }
199:
200: //----------------------------------------------------------------------------
201:
202: /**
203: * <p>Get Root Caetgories and their names as a DBSubset</p>
204: * Categories not having any translation at k_cat_labels will not be retrieved.<br>
205: * Root Category Names are loaded once and then cached internally as a static object.<br>
206: * Use clearCahce() method for refreshing root categories from database.
207: * @param oConn Database Connection
208: * @param sLanguage Language for category label retrieval.
209: * @param iOrderBy Column for order by { ORDER_BY_NONE, ORDER_BY_NEUTRAL_NAME, ORDER_BY_LOCALE_NAME }
210: * @return A DBSubset with the following columns:<br>
211: * <table border=1 cellpadding=4>
212: * <tr><td><b>gu_category</b></td><td><b>nm_category</b></td><td><b>tr_category</b></td><td><b>nm_icon</b></td><td><b>nm_icon2</b></td></tr>
213: * <tr><td>Category GUID</td><td>Category Internal Name</td><td>Category Translated Label</td><td>Icon for Closed Folder</td><td>Icon for Opened Folder</td></tr>
214: * </table>
215: * @throws SQLException
216: */
217: public DBSubset getRootsNamed(JDCConnection oConn,
218: String sLanguage, int iOrderBy) throws SQLException {
219:
220: sRootsNamedFields = "c." + DB.gu_category + ", c."
221: + DB.nm_category + ", " + DBBind.Functions.ISNULL
222: + "(n." + DB.tr_category + ",''), c." + DB.nm_icon
223: + ", c." + DB.nm_icon2;
224:
225: if (DebugFile.trace) {
226: DebugFile
227: .writeln("Begin Categories.getRootsNamed([Connection], "
228: + sLanguage
229: + String.valueOf(iOrderBy)
230: + ")");
231: DebugFile.incIdent();
232: }
233:
234: if (!oRootsLoaded) {
235: Object[] aLang = { sLanguage };
236:
237: if (iOrderBy > 0)
238: oRootsNamed = new DBSubset(sRootsNamedTables,
239: sRootsNamedFields, sRootsNamedFilter
240: + " ORDER BY " + iOrderBy, 16);
241: else
242: oRootsNamed = new DBSubset(sRootsNamedTables,
243: sRootsNamedFields, sRootsNamedFilter, 16);
244:
245: iRootsCount = oRootsNamed.load(oConn, aLang);
246: oRootsLoaded = true;
247: }
248:
249: if (DebugFile.trace) {
250: DebugFile.decIdent();
251: DebugFile.writeln("End Categories.getRootsNamed()");
252: }
253:
254: return oRootsNamed;
255: } // getRootsNamed()
256:
257: //----------------------------------------------------------------------------
258:
259: /**
260: * <p>Get first level childs for a given category.</p>
261: * Categories not having any translation at k_cat_labels will not be retrieved.
262: * @param oConn Database Connection
263: * @param idParent Parent Category
264: * @param sLanguage Language for label retrieval
265: * @param iOrderBy Column for order by { ORDER_BY_NONE, ORDER_BY_NEUTRAL_NAME, ORDER_BY_LOCALE_NAME }
266: * @return A DBSubset with the following columns:<br>
267: * <table border=1 cellpadding=4>
268: * <tr><td><b>gu_category</b></td><td><b>nm_category</b></td><td><b>tr_category</b></td><td><b>nm_icon</b></td><td><b>nm_icon2</b></td></tr>
269: * <tr><td>Category GUID</td><td>Category Internal Name</td><td>Category Translated Label</td><td>Icon for Closed Folder</td><td>Icon for Opened Folder</td></tr>
270: * </table>
271: * @throws SQLException
272: */
273: public DBSubset getChildsNamed(JDCConnection oConn,
274: String idParent, String sLanguage, int iOrderBy)
275: throws SQLException {
276:
277: long lElapsed = 0;
278:
279: if (DebugFile.trace) {
280: lElapsed = System.currentTimeMillis();
281: DebugFile
282: .writeln("Begin Categories.getChildsNamed([Connection], "
283: + (idParent == null ? "null" : idParent)
284: + ","
285: + (sLanguage == null ? "null" : sLanguage)
286: + "," + String.valueOf(iOrderBy) + ")");
287: DebugFile.incIdent();
288: }
289:
290: Object[] aParams = { idParent, sLanguage, idParent, idParent,
291: sLanguage };
292: DBSubset oChilds;
293:
294: if (iOrderBy > 0)
295: oChilds = new DBSubset(sChildNamedTables,
296: sChildNamedFields, sChildNamedFilter
297: + " UNION SELECT " + "c." + DB.gu_category
298: + ",c." + DB.nm_category + ",c."
299: + DB.nm_category + "," + "c." + DB.nm_icon
300: + ",c." + DB.nm_icon2 + " FROM "
301: + DB.k_categories + " c, " + DB.k_cat_tree
302: + " t WHERE c." + DB.gu_category + "=t."
303: + DB.gu_child_cat + " AND " + "t."
304: + DB.gu_parent_cat + "=? AND c."
305: + DB.gu_category + " NOT IN " + "(SELECT "
306: + DB.gu_category + " FROM "
307: + sChildNamedTables + " WHERE "
308: + sChildNamedFilter + ") ORDER BY "
309: + iOrderBy, 32);
310: else
311: oChilds = new DBSubset(sChildNamedTables,
312: sChildNamedFields, sChildNamedFilter
313: + " UNION SELECT " + "c." + DB.gu_category
314: + ",c." + DB.nm_category + ",c."
315: + DB.nm_category + ", " + "c." + DB.nm_icon
316: + ",c." + DB.nm_icon2 + " FROM "
317: + DB.k_categories + " c, " + DB.k_cat_tree
318: + " t WHERE c." + DB.gu_category + "=t."
319: + DB.gu_child_cat + " AND " + "t."
320: + DB.gu_parent_cat + "=? AND c."
321: + DB.gu_category + " NOT IN " + "(SELECT "
322: + DB.gu_category + " FROM "
323: + sChildNamedTables + " WHERE "
324: + sChildNamedFilter + ")", 32);
325:
326: int iChilds = oChilds.load(oConn, aParams);
327:
328: if (DebugFile.trace) {
329: DebugFile.writeln(String.valueOf(iChilds)
330: + " childs readed in "
331: + String.valueOf(System.currentTimeMillis()
332: - lElapsed) + " ms");
333: DebugFile.decIdent();
334: DebugFile.writeln("End Categories.getChildsNamed()");
335: }
336:
337: return oChilds;
338: } // getChildsNamed()
339:
340: //----------------------------------------------------------------------------
341:
342: private DBSubset oRoots;
343:
344: private DBSubset oRootsNamed;
345: private boolean oRootsLoaded;
346: private int iRootsCount;
347: private String sRootsNamedTables;
348: private String sRootsNamedFields;
349: private String sRootsNamedFilter;
350:
351: private String sChildNamedTables;
352: private String sChildNamedFields;
353: private String sChildNamedFilter;
354: private String sChildNamedNoLang;
355:
356: public static final int ORDER_BY_NONE = 0;
357: public static final int ORDER_BY_ID = 1;
358: public static final int ORDER_BY_NEUTRAL_NAME = 2;
359: public static final int ORDER_BY_LOCALE_NAME = 3;
360: }
|