001: /*
002: * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: BlogDatabaseSchema.java,v 1.6 2007/02/20 02:14:49 bastafidli Exp $
007: *
008: * This program is free software; you can redistribute it and/or modify
009: * it under the terms of the GNU General Public License as published by
010: * the Free Software Foundation; version 2 of the License.
011: *
012: * This program is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
015: * GNU General Public License for more details.
016: *
017: * You should have received a copy of the GNU General Public License
018: * along with this program; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: */
021:
022: package org.opensubsystems.blog.persist.db;
023:
024: import java.sql.Connection;
025: import java.sql.SQLException;
026: import java.util.HashMap;
027: import java.util.Map;
028:
029: import org.opensubsystems.blog.data.Blog;
030: import org.opensubsystems.blog.data.Entry;
031: import org.opensubsystems.core.data.DataConstant;
032: import org.opensubsystems.core.error.OSSException;
033: import org.opensubsystems.core.error.OSSInvalidContextException;
034: import org.opensubsystems.core.error.OSSInvalidDataException;
035: import org.opensubsystems.core.persist.db.DatabaseImpl;
036: import org.opensubsystems.core.util.Messages;
037: import org.opensubsystems.patterns.listdata.persist.db.impl.ModifiableListDatabaseSchemaImpl;
038:
039: /**
040: * Database specific operations related to persistence of blogs and their entries.
041: *
042: * @version $Id: BlogDatabaseSchema.java,v 1.6 2007/02/20 02:14:49 bastafidli Exp $
043: * @author Miro Halas
044: * @code.reviewer Miro Halas
045: * @code.reviewed 1.6 2006/07/13 23:43:28 jlegeny
046: */
047: public abstract class BlogDatabaseSchema extends
048: ModifiableListDatabaseSchemaImpl {
049: // Constants ////////////////////////////////////////////////////////////////
050:
051: /**
052: * Name identifies this schema in the database.
053: */
054: public static final String BLOG_SCHEMA_NAME = "BLOG";
055:
056: /**
057: * Version of this schema in the database.
058: */
059: public static final int BLOG_SCHEMA_VERSION = 1;
060:
061: // Blog related constants
062:
063: /**
064: * Table name where blog informations are stored.
065: */
066: public static final String BLOG_TABLE_NAME = "BF_BLOG";
067:
068: /**
069: * All columns in a blog table in the order in which they will be retrieved
070: */
071: public static final String BLOG_COLUMNS = "ID, DOMAIN_ID, FOLDER, CAPTION,"
072: + " COMMENTS, CREATION_DATE," + " MODIFICATION_DATE";
073:
074: /**
075: * Columns which always have to be retrieved from the database.
076: * We always need data object id and domain id to correctly identify the
077: * object and modification date to provide support for optimistic locking.
078: * Caption is the allowing user to access defails of the blog.
079: */
080: public static final int[] BLOG_MANDATORY_RETRIEVE_COLUMNS = {
081: Blog.COL_BLOG_ID, Blog.COL_BLOG_DOMAIN_ID,
082: Blog.COL_BLOG_MODIFICATION_DATE, Blog.COL_BLOG_CAPTION, };
083:
084: /**
085: * Static variable for array of all columns codes that can be used for
086: * sorting.
087: */
088: public static final int[] BLOG_SORT_COLUMNS = {
089: Blog.COL_BLOG_FOLDER, Blog.COL_BLOG_CAPTION,
090: Blog.COL_BLOG_COMMENTS, Blog.COL_BLOG_CREATION_DATE,
091: Blog.COL_BLOG_MODIFICATION_DATE, };
092:
093: /**
094: * Static variable for array of all columns codes that can be used for
095: * filtering.
096: */
097: public static final int[] BLOG_FILTER_COLUMNS = {
098: Blog.COL_BLOG_FOLDER, Blog.COL_BLOG_CAPTION,
099: Blog.COL_BLOG_COMMENTS, Blog.COL_BLOG_CREATION_DATE,
100: Blog.COL_BLOG_MODIFICATION_DATE, };
101:
102: /**
103: * Maximal length of blog folder.
104: */
105: public static final int BLOG_FOLDER_MAXLENGTH = 50;
106:
107: /**
108: * Maximal length of blog caption.
109: */
110: public static final int BLOG_CAPTION_MAXLENGTH = 1024;
111:
112: /**
113: * Maximal length of blog comments.
114: */
115: public static final int BLOG_COMMENTS_MAXLENGTH = 4000;
116:
117: // Blog entry related constants
118:
119: /**
120: * Table name where blog entry informations are stored.
121: */
122: public static final String BLOGENTRY_TABLE_NAME = "BF_BLOG_ENTRY";
123:
124: /**
125: * All columns in a entry table in the order in which they will be retrieved
126: */
127: public static final String ENTRY_COLUMNS = "ID, DOMAIN_ID, BLOG_ID, CAPTION,"
128: + " COMMENTS, IMAGEURL, TARGETURL,"
129: + " CREATION_DATE, MODIFICATION_DATE";
130:
131: /**
132: * Columns which always have to be retrieved from the database.
133: * We always need data object id and domain id to correctly identify the
134: * object and modification date to provide support for optimistic locking.
135: * Caption is the allowing user to access defails of the entry and parent id
136: * allows access to the blog.
137: */
138: public static final int[] BLOGENTRY_MANDATORY_RETRIEVE_COLUMNS = {
139: Entry.COL_BLOGENTRY_ID, Entry.COL_BLOGENTRY_DOMAIN_ID,
140: Entry.COL_BLOGENTRY_MODIFICATION_DATE,
141: Entry.COL_BLOGENTRY_PARENT_ID, Entry.COL_BLOGENTRY_CAPTION, };
142:
143: /**
144: * Static variable for array of all columns codes that can be used for
145: * sorting.
146: */
147: public static final int[] BLOGENTRY_SORT_COLUMNS = {
148: Entry.COL_BLOGENTRY_CAPTION, Entry.COL_BLOGENTRY_COMMENTS,
149: Entry.COL_BLOGENTRY_IMAGEURL,
150: Entry.COL_BLOGENTRY_TARGETURL,
151: Entry.COL_BLOGENTRY_CREATION_DATE,
152: Entry.COL_BLOGENTRY_MODIFICATION_DATE, };
153:
154: /**
155: * Static variable for array of all columns codes that can be used for
156: * filtering.
157: */
158: public static final int[] BLOGENTRY_FILTER_COLUMNS = {
159: Entry.COL_BLOGENTRY_CAPTION, Entry.COL_BLOGENTRY_COMMENTS,
160: Entry.COL_BLOGENTRY_IMAGEURL,
161: Entry.COL_BLOGENTRY_TARGETURL,
162: Entry.COL_BLOGENTRY_CREATION_DATE,
163: Entry.COL_BLOGENTRY_MODIFICATION_DATE, };
164:
165: /**
166: * Maximal length of entry caption.
167: */
168: public static final int BLOGENTRY_CAPTION_MAXLENGTH = 1024;
169:
170: /**
171: * Maximal length of entry comments.
172: */
173: public static final int BLOGENTRY_COMMENTS_MAXLENGTH = 4000;
174:
175: /**
176: * Maximal length of entry image URL.
177: */
178: public static final int BLOGENTRY_IMAGEURL_MAXLENGTH = 1024;
179:
180: /**
181: * Maximal length of entry target URL.
182: */
183: public static final int BLOGENTRY_TARGETURL_MAXLENGTH = 1024;
184:
185: // Cached values ////////////////////////////////////////////////////////////
186:
187: /**
188: * Map of table names belonging to this schema.
189: */
190: public static final Map TABLE_NAMES;
191:
192: /**
193: * Map of columns that always have to be retrieved.
194: */
195: public static final Map MANDATORY_RETRIEVE_COLUMNS;
196:
197: /**
198: * Map of columns that can be used for sorting.
199: */
200: public static final Map SORTABLE_COLUMNS;
201:
202: /**
203: * Map of columns that can be used for filtering.
204: */
205: public static final Map FILTERABLE_COLUMNS;
206:
207: // Constructors /////////////////////////////////////////////////////////////
208:
209: /**
210: * Static initializer
211: */
212: static {
213: // Create map that stores table names. The key is object data type
214: // and value is table name
215: TABLE_NAMES = new HashMap();
216: TABLE_NAMES.put(DataConstant.BLOG_DATA_TYPE_OBJ,
217: BLOG_TABLE_NAME);
218: TABLE_NAMES.put(DataConstant.BLOGENTRY_DATA_TYPE_OBJ,
219: BLOGENTRY_TABLE_NAME);
220:
221: MANDATORY_RETRIEVE_COLUMNS = new HashMap();
222: MANDATORY_RETRIEVE_COLUMNS.put(DataConstant.BLOG_DATA_TYPE_OBJ,
223: BLOG_MANDATORY_RETRIEVE_COLUMNS);
224: MANDATORY_RETRIEVE_COLUMNS.put(
225: DataConstant.BLOGENTRY_DATA_TYPE_OBJ,
226: BLOGENTRY_MANDATORY_RETRIEVE_COLUMNS);
227:
228: SORTABLE_COLUMNS = new HashMap();
229: SORTABLE_COLUMNS.put(DataConstant.BLOG_DATA_TYPE_OBJ,
230: BLOG_SORT_COLUMNS);
231: SORTABLE_COLUMNS.put(DataConstant.BLOGENTRY_DATA_TYPE_OBJ,
232: BLOGENTRY_SORT_COLUMNS);
233:
234: FILTERABLE_COLUMNS = new HashMap();
235: FILTERABLE_COLUMNS.put(DataConstant.BLOG_DATA_TYPE_OBJ,
236: BLOG_FILTER_COLUMNS);
237: FILTERABLE_COLUMNS.put(DataConstant.BLOGENTRY_DATA_TYPE_OBJ,
238: BLOGENTRY_FILTER_COLUMNS);
239:
240: // Setup maximal length of individual fields for entities
241: Blog.setFolderMaxLength(BLOG_FOLDER_MAXLENGTH);
242: Blog.setCaptionMaxLength(BLOG_CAPTION_MAXLENGTH);
243: Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH);
244: Entry.setCaptionMaxLength(BLOGENTRY_CAPTION_MAXLENGTH);
245: Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH);
246: Entry.setImageURLMaxLength(BLOGENTRY_IMAGEURL_MAXLENGTH);
247: Entry.setTargetURLMaxLength(BLOGENTRY_TARGETURL_MAXLENGTH);
248: }
249:
250: /**
251: * Default constructor.
252: *
253: * @throws OSSException - error occured.
254: */
255: public BlogDatabaseSchema() throws OSSException {
256: super (null, BLOG_SCHEMA_NAME, BLOG_SCHEMA_VERSION, true,
257: TABLE_NAMES, TABLE_NAMES, MANDATORY_RETRIEVE_COLUMNS,
258: SORTABLE_COLUMNS, FILTERABLE_COLUMNS);
259: }
260:
261: // Public methods ///////////////////////////////////////////////////////////
262:
263: /**
264: * Get query to load blog by id
265: *
266: * @param strColumns - list of specific columns to retrieve
267: * @return String - query
268: * @throws OSSException - an error has occured
269: */
270: public String getSelectBlogById(String strColumns)
271: throws OSSException {
272: StringBuffer buffer = new StringBuffer();
273:
274: buffer.append("select ");
275: buffer.append(strColumns);
276: buffer.append(" from BF_BLOG where ID = ? and DOMAIN_ID = ?");
277:
278: return buffer.toString();
279: }
280:
281: /**
282: * Get query to load blog by folder
283: *
284: * @param strColumns - list of specific columns to retrieve
285: * @return String - query
286: * @throws OSSException - an error has occured
287: */
288: public String getSelectBlogByFolder(String strColumns)
289: throws OSSException {
290: StringBuffer buffer = new StringBuffer();
291:
292: buffer.append("select ");
293: buffer.append(strColumns);
294: buffer
295: .append(" from BF_BLOG where FOLDER = ? and DOMAIN_ID = ?");
296:
297: return buffer.toString();
298: }
299:
300: /**
301: * Get query that inserts a blog to the database and fetches database
302: * generated values such as the generated id and creation timestamp
303: *
304: * @return String - query for simple insert or stored procedure call
305: * @throws OSSException - an error has occured
306: */
307: public abstract String getInsertBlogAndFetchGeneratedValues()
308: throws OSSException;
309:
310: /**
311: * Get query that updates blog in the database and fetches database
312: * generated values such as the updated modification timestamp
313: *
314: * @return String - query for simple update or stored procedure call
315: * @throws OSSException - an error has occured
316: */
317: public abstract String getUpdateBlogAndFetchGeneratedValues()
318: throws OSSException;
319:
320: /**
321: * Method returns simple insert blog query. This method is common for all
322: * databases and can be overwritten for each specific database schema.
323: *
324: * @return String - simple insert blog query
325: * @throws OSSException - an error has occured
326: */
327: public String getInsertBlog() throws OSSException {
328: StringBuffer buffer = new StringBuffer();
329:
330: // No ID column is specified here by default since it is generated by the
331: // database and therefore we do not know value for it
332: buffer
333: .append("insert into BF_BLOG(DOMAIN_ID, FOLDER, CAPTION, COMMENTS,"
334: + " CREATION_DATE, MODIFICATION_DATE)"
335: + " values (?, ?, ?, ?, ");
336: buffer.append(DatabaseImpl.getInstance()
337: .getCurrentTimestampFunctionCall());
338: buffer.append(",");
339: buffer.append(DatabaseImpl.getInstance()
340: .getCurrentTimestampFunctionCall());
341: buffer.append(")");
342:
343: return buffer.toString();
344: }
345:
346: /**
347: * Get query to delete blog by id
348: *
349: * @return String - query
350: */
351: public String getDeleteBlogById() {
352: return "delete from BF_BLOG where ID = ? and DOMAIN_ID = ?";
353: }
354:
355: /**
356: * Get query to load entry by id
357: *
358: * @param strColumns - list of specific columns to retrieve
359: * @return String - query
360: * @throws OSSException - an error has occured
361: */
362: public String getSelectEntryById(String strColumns)
363: throws OSSException {
364: StringBuffer buffer = new StringBuffer();
365:
366: buffer.append("select ");
367: buffer.append(strColumns);
368: buffer
369: .append(" from BF_BLOG_ENTRY where ID = ? and DOMAIN_ID = ?");
370:
371: return buffer.toString();
372: }
373:
374: /**
375: * Get query to load last created entry
376: *
377: * @param strColumns - list of specific columns to retrieve
378: * @return String - query
379: * @throws OSSException - an error has occured
380: */
381: public String getSelectLastEntry(String strColumns)
382: throws OSSException {
383: StringBuffer buffer = new StringBuffer();
384:
385: buffer.append("select ");
386: buffer.append(strColumns);
387: // Last entry has to have the highest id
388: buffer
389: .append(" from BF_BLOG_ENTRY where ID = (select max(ID)"
390: + " from BF_BLOG_ENTRY where BLOG_ID = ? and DOMAIN_ID = ?)");
391:
392: return buffer.toString();
393:
394: }
395:
396: /**
397: * Get query that inserts a entry to the database and fetches database
398: * generated values such as the generated id and creation timestamp
399: *
400: * @return String - query for simple insert or stored procedure call
401: * @throws OSSException - an error has occured
402: */
403: public abstract String getInsertEntryAndFetchGeneratedValues()
404: throws OSSException;
405:
406: /**
407: * Get query that updates entry in the database and fetches database
408: * generated values such as the updated modification timestamp
409: *
410: * @return String - query for simple update or stored procedure call
411: * @throws OSSException - an error has occured
412: */
413: public abstract String getUpdateEntryAndFetchGeneratedValues()
414: throws OSSException;
415:
416: /**
417: * Method returns simple insert entry query. This method is common for all
418: * databases and can be overwritten for each specific database schema.
419: *
420: * @return String - simple insert entry query
421: * @throws OSSException - an error has occured
422: */
423: public String getInsertEntry() throws OSSException {
424: StringBuffer buffer = new StringBuffer();
425:
426: // No ID column is specified here by default since it is generated by the
427: // database and therefore we do not know value for it
428: buffer
429: .append("insert into BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, CAPTION,"
430: + " COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)"
431: + " values (?, ?, ?, ?, ?, ?, ");
432: buffer.append(DatabaseImpl.getInstance()
433: .getCurrentTimestampFunctionCall());
434: buffer.append(",");
435: buffer.append(DatabaseImpl.getInstance()
436: .getCurrentTimestampFunctionCall());
437: buffer.append(")");
438:
439: return buffer.toString();
440: }
441:
442: /**
443: * Get query to delete entry by id
444: *
445: * @return String - query
446: */
447: public String getDeleteEntryById() {
448: return "delete from BF_BLOG_ENTRY where ID = ? and DOMAIN_ID = ?";
449: }
450:
451: /**
452: * {@inheritDoc}
453: */
454: public void handleSQLException(SQLException exc,
455: Connection dbConnection, int iOperationType, int iDataType,
456: Object data) throws OSSException {
457: OSSInvalidDataException ideException = null;
458:
459: switch (iOperationType) {
460: case DBOP_INSERT: {
461: if (iDataType == DataConstant.BLOG_DATA_TYPE) {
462: if ((exc.getMessage().toUpperCase().indexOf(
463: "BF_BLOG_FLDR_UQ") > -1)
464: // MySQL handles blog folder unique constraint exception as 'KEY 2'
465: || ((exc.getMessage().toUpperCase())
466: .endsWith("KEY 2"))
467: // IBM DB2 handles blog folder unique constraint exception as "2"
468: || ((exc.getMessage().toUpperCase())
469: .indexOf("\"2\"") > -1)) {
470: ideException = OSSInvalidDataException
471: .addException(ideException,
472: Messages.NONSPECIFIC_ERRORS,
473: "Folder has to be unique.", exc);
474: }
475: } else if (iDataType == DataConstant.BLOGENTRY_DATA_TYPE) {
476: if (exc.getMessage().toUpperCase().indexOf(
477: "BF_BLOGENTR_FK") > -1) {
478: throw new OSSInvalidContextException(
479: "Blog to create entry in does not exist.",
480: exc);
481: }
482: }
483: break;
484: }
485: case DBOP_UPDATE: {
486: if (iDataType == DataConstant.BLOG_DATA_TYPE) {
487: if ((exc.getMessage().toUpperCase().indexOf(
488: "BF_BLOG_FLDR_UQ") > -1)
489: // MySQL handles blog folder unique constraint exception as 'KEY 2'
490: || ((exc.getMessage().toUpperCase())
491: .endsWith("KEY 2"))
492: // IBM DB2 handles blog folder unique constraint exception as "2"
493: || ((exc.getMessage().toUpperCase())
494: .indexOf("\"2\"") > -1)) {
495: ideException = OSSInvalidDataException
496: .addException(ideException,
497: Messages.NONSPECIFIC_ERRORS,
498: "Folder has to be unique.", exc);
499: }
500: } else if (iDataType == DataConstant.BLOGENTRY_DATA_TYPE) {
501: // No special error checking
502: }
503: break;
504: }
505: default: {
506: // Some operations do not need any specific handling
507: }
508: }
509: if (ideException != null) {
510: // If a specific exception was created throw it back to client
511: throw ideException;
512: } else {
513: // No special handling was needed so execute the default exception handling
514: super .handleSQLException(exc, dbConnection, iOperationType,
515: iDataType, data);
516: }
517: }
518:
519: // List operations //////////////////////////////////////////////////////////
520:
521: /**
522: * {@inheritDoc}
523: */
524: public StringBuffer getColumns(final boolean specific,
525: final int[] columns, final Object[] prefixes,
526: final Object[] postfixes, StringBuffer buffer)
527: throws OSSException {
528: if (buffer == null) {
529: buffer = new StringBuffer();
530: }
531: for (int iIndex = 0; iIndex < columns.length; iIndex++) {
532: if (iIndex > 0) {
533: buffer.append(",");
534: }
535:
536: if ((prefixes != null) && (prefixes.length > 0)
537: && (prefixes[iIndex] != null)) {
538: buffer.append(prefixes[iIndex]);
539: }
540:
541: if (specific) {
542: if ((columns[iIndex] >= Blog.COL_BLOG_ID)
543: && (columns[iIndex] <= Blog.COL_BLOG_MODIFICATION_DATE)) {
544: buffer.append(BLOG_TABLE_NAME);
545: buffer.append(".");
546: } else if (((columns[iIndex] >= Entry.COL_BLOGENTRY_ID) && (columns[iIndex] <= Entry.COL_BLOGENTRY_MODIFICATION_DATE))
547: || (columns[iIndex] == DataConstant.BLOG_DATA_TYPE)) {
548: buffer.append(BLOGENTRY_TABLE_NAME);
549: buffer.append(".");
550: } else {
551: assert false : "Unknown column ID "
552: + columns[iIndex];
553: }
554: }
555:
556: switch (columns[iIndex]) {
557: case (Blog.COL_BLOG_ID):
558: case (Entry.COL_BLOGENTRY_ID): {
559: buffer.append("ID");
560: break;
561: }
562: case (Blog.COL_BLOG_DOMAIN_ID):
563: case (Entry.COL_BLOGENTRY_DOMAIN_ID): {
564: buffer.append("DOMAIN_ID");
565: break;
566: }
567: case (Blog.COL_BLOG_CAPTION):
568: case (Entry.COL_BLOGENTRY_CAPTION): {
569: buffer.append("CAPTION");
570: break;
571: }
572: case (Blog.COL_BLOG_COMMENTS):
573: case (Entry.COL_BLOGENTRY_COMMENTS): {
574: buffer.append("COMMENTS");
575: break;
576: }
577: case (Blog.COL_BLOG_CREATION_DATE):
578: case (Entry.COL_BLOGENTRY_CREATION_DATE): {
579: buffer.append("CREATION_DATE");
580: break;
581: }
582: case (Blog.COL_BLOG_MODIFICATION_DATE):
583: case (Entry.COL_BLOGENTRY_MODIFICATION_DATE): {
584: buffer.append("MODIFICATION_DATE");
585: break;
586: }
587: case (Blog.COL_BLOG_FOLDER): {
588: buffer.append("FOLDER");
589: break;
590: }
591: case (DataConstant.BLOG_DATA_TYPE):
592: case (Entry.COL_BLOGENTRY_PARENT_ID): {
593: buffer.append("BLOG_ID");
594: break;
595: }
596: case (Entry.COL_BLOGENTRY_IMAGEURL): {
597: buffer.append("IMAGEURL");
598: break;
599: }
600: case (Entry.COL_BLOGENTRY_TARGETURL): {
601: buffer.append("TARGETURL");
602: break;
603: }
604: default: {
605: assert false : "Unknown column ID " + columns[iIndex];
606: }
607: }
608:
609: if ((postfixes != null) && (postfixes.length > 0)
610: && (postfixes[iIndex] != null)) {
611: buffer.append(postfixes[iIndex]);
612: }
613: }
614:
615: return buffer;
616: }
617: }
|