001: /*
002: * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: SybaseBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:19 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.sybase;
023:
024: import java.sql.Connection;
025: import java.sql.PreparedStatement;
026: import java.sql.SQLException;
027: import java.sql.Statement;
028: import java.util.logging.Level;
029: import java.util.logging.Logger;
030:
031: import org.opensubsystems.blog.data.Blog;
032: import org.opensubsystems.blog.data.Entry;
033: import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
034: import org.opensubsystems.core.data.DataConstant;
035: import org.opensubsystems.core.error.OSSException;
036: import org.opensubsystems.core.util.CallContext;
037: import org.opensubsystems.core.util.DatabaseUtils;
038: import org.opensubsystems.core.util.Log;
039:
040: /**
041: * Database specific operations related to persistence of blogs and their
042: * entries. This class encapsulate functionality specific for MS SQL Server.
043: *
044: * @version $Id: SybaseBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:19 bastafidli Exp $
045: * @author Julian Legeny
046: * @code.reviewer Miro Halas
047: * @code.reviewed 1.3 2006/07/21 00:54:56 jlegeny
048: */
049: public class SybaseBlogDatabaseSchema extends BlogDatabaseSchema {
050: /*
051: Use autogenerated numbers for IDs using sequence
052: Name all constraints to easily identify them later.
053: ON DELETE CASCADE must be provided by trigger (but there can by just 1
054: delete trigger for the table). There will be not used triggers and all
055: data will be deleted manually.
056:
057: CREATE TABLE BF_BLOG
058: (
059: ID NUMERIC(10, 0) IDENTITY NOT NULL,
060: DOMAIN_ID NUMERIC(10, 0) NOT NULL,
061: FOLDER VARCHAR(50) NOT NULL,
062: CAPTION VARCHAR(1024) NOT NULL,
063: COMMENTS VARCHAR(8000) NOT NULL,
064: CREATION_DATE DATETIME NOT NULL,
065: MODIFICATION_DATE DATETIME NOT NULL,
066: CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
067: // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
068: // REFERENCES BF_DOMAIN (ID),
069: CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
070: )
071:
072:
073: CREATE PROCEDURE INSERT_BF_BLOG
074: @IN_DOMAIN_ID NUMERIC(10, 0),
075: @IN_FOLDER VARCHAR(50),
076: @IN_CAPTION VARCHAR(1024),
077: @IN_COMMENTS VARCHAR(8000),
078: @OUT_KEY INTEGER OUTPUT,
079: @OUT_TIMESTAMP DATETIME OUTPUT
080: AS
081: SET @OUT_TIMESTAMP = GETDATE()
082: INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER,
083: CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
084: VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION,
085: @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP)
086: SET @OUT_KEY = @@IDENTITY
087: RETURN
088:
089:
090: CREATE PROCEDURE UPDATE_BF_BLOG
091: @IN_FOLDER VARCHAR(50),
092: @IN_CAPTION VARCHAR(1024),
093: @IN_COMMENTS VARCHAR(8000),
094: @IN_BLOG_ID NUMERIC(10, 0),
095: @IN_DOMAIN_ID NUMERIC(10, 0),
096: @IN_MODIFICATION_DATE DATETIME,
097: @OUT_TIMESTAMP DATETIME
098: AS
099: SET @OUT_TIMESTAMP = GETDATE()
100: UPDATE BF_BLOG SET FOLDER = @IN_FOLDER, CAPTION = @IN_CAPTION,
101: COMMENTS = @IN_COMMENTS, MODIFICATION_DATE = @OUT_TIMESTAMP
102: WHERE ID = @IN_BLOG_ID AND DOMAIN_ID = @IN_DOMAIN_ID
103: AND MODIFICATION_DATE = @IN_MODIFICATION_DATE
104: RETURN
105:
106:
107: CREATE TABLE BF_BLOG_ENTRY
108: (
109: ID NUMERIC(10, 0) IDENTITY NOT NULL,
110: DOMAIN_ID NUMERIC(10, 0) NOT NULL,
111: BLOG_ID NUMERIC(10, 0) NOT NULL,
112: CAPTION VARCHAR(1024) NOT NULL,
113: COMMENTS VARCHAR(8000) NOT NULL,
114: IMAGEURL VARCHAR(1024) NOT NULL,
115: TARGETURL VARCHAR(1024) NOT NULL,
116: CREATION_DATE DATETIME NOT NULL,
117: MODIFICATION_DATE DATETIME NOT NULL,
118: CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
119: // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
120: // REFERENCES BF_DOMAIN (ID),
121: CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
122: REFERENCES BF_BLOG (ID)
123: )
124:
125:
126: CREATE PROCEDURE INSERT_BF_BLOGENTR
127: @IN_DOMAIN_ID NUMERIC(10, 0),
128: @IN_BLOG_ID NUMERIC(10, 0),
129: @IN_CAPTION VARCHAR(1024),
130: @IN_COMMENTS VARCHAR(8000),
131: @IN_IMAGEURL VARCHAR(1024) NOT NULL,
132: @IN_TARGETURL VARCHAR(1024) NOT NULL,
133: @OUT_KEY INTEGER OUTPUT,
134: @OUT_TIMESTAMP DATETIME OUTPUT
135: AS
136: SET @OUT_TIMESTAMP = GETDATE()
137: INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID,
138: CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
139: VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION,
140: @IN_COMMENTS, @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP)
141: SET @OUT_KEY = @@IDENTITY
142: RETURN
143:
144:
145: CREATE PROCEDURE UPDATE_BF_BLOGENTR
146: @IN_CAPTION VARCHAR(1024),
147: @IN_COMMENTS VARCHAR(8000),
148: @IN_IMAGEURL VARCHAR(1024) NOT NULL,
149: @IN_TARGETURL VARCHAR(1024) NOT NULL,
150: @IN_ENTRY_ID NUMERIC(10, 0),
151: @IN_DOMAIN_ID NUMERIC(10, 0),
152: @IN_MODIFICATION_DATE DATETIME,
153: @OUT_TIMESTAMP DATETIME OUTPUT
154: AS
155: SET @OUT_TIMESTAMP = GETDATE()
156: UPDATE BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS,
157: IMAGEURL = @IN_IMAGEURL, TARGETURL= @IN_TARGETURL,
158: MODIFICATION_DATE = @OUT_TIMESTAMP
159: WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID
160: AND MODIFICATION_DATE = @IN_MODIFICATION_DATE
161: RETURN
162: */
163:
164: // Constants ////////////////////////////////////////////////////////////////
165: /**
166: * Maximal length of blog comments.
167: * Restriction for VARCHAR type is max. 8000 characters for Sybase database.
168: */
169: public static final int BLOG_COMMENTS_MAXLENGTH_SYBASE = 8000;
170:
171: /**
172: * Maximal length of blog entry comments.
173: * Restriction for VARCHAR type is max. 8000 characters for Sybase database.
174: */
175: public static final int BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE = 8000;
176:
177: // Cached values ////////////////////////////////////////////////////////////
178:
179: /**
180: * Logger for this class
181: */
182: private static Logger s_logger = Log
183: .getInstance(SybaseBlogDatabaseSchema.class);
184:
185: // Constructors /////////////////////////////////////////////////////////////
186:
187: /**
188: * Static initializer
189: */
190: static {
191: // Setup maximal length of individual fields for Sybase database.
192: Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_SYBASE);
193: Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE);
194: }
195:
196: /**
197: * Default constructor.
198: *
199: * @throws OSSException - error occured.
200: */
201: public SybaseBlogDatabaseSchema() throws OSSException {
202: super ();
203: }
204:
205: // Public methods ///////////////////////////////////////////////////////////
206:
207: /**
208: * {@inheritDoc}
209: */
210: public void create(Connection cntDBConnection, String strUserName)
211: throws SQLException {
212: Statement stmQuery = null;
213: try {
214: stmQuery = cntDBConnection.createStatement();
215:
216: if (stmQuery.execute("create table BF_BLOG" + NL + "(" + NL
217: + " ID NUMERIC(10, 0) IDENTITY NOT NULL," + NL
218: + " DOMAIN_ID NUMERIC(10, 0) NOT NULL," + NL
219: + " FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH
220: + ") NOT NULL," + NL + " CAPTION VARCHAR("
221: + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL
222: + " COMMENTS VARCHAR("
223: + BLOG_COMMENTS_MAXLENGTH_SYBASE + ") NOT NULL,"
224: + NL + " CREATION_DATE DATETIME NOT NULL," + NL
225: + " MODIFICATION_DATE DATETIME NOT NULL,"
226: + NL
227: + " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),"
228: + NL
229: +
230: // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
231: // " REFERENCES BF_DOMAIN (ID)," + NL +
232: " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)"
233: + NL + ")")) {
234: // Close any results
235: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
236: }
237: s_logger.log(Level.FINEST, "Table BF_BLOG created.");
238:
239: ///////////////////////////////////////////////////////////////////////
240:
241: if (stmQuery
242: .execute("CREATE PROCEDURE INSERT_BF_BLOG "
243: + NL
244: + " @IN_DOMAIN_ID NUMERIC(10, 0), "
245: + NL
246: + " @IN_FOLDER VARCHAR("
247: + BLOG_FOLDER_MAXLENGTH
248: + "), "
249: + NL
250: + " @IN_CAPTION VARCHAR("
251: + BLOG_CAPTION_MAXLENGTH
252: + "), "
253: + NL
254: + " @IN_COMMENTS VARCHAR("
255: + BLOG_COMMENTS_MAXLENGTH_SYBASE
256: + "), "
257: + NL
258: + " @OUT_KEY INTEGER OUTPUT, "
259: + NL
260: + " @OUT_TIMESTAMP DATETIME OUTPUT "
261: + NL
262: + "AS "
263: + NL
264: + " SET @OUT_TIMESTAMP = GETDATE() "
265: + NL
266: + " INSERT INTO "
267: + strUserName
268: + ".BF_BLOG(DOMAIN_ID, FOLDER, "
269: + NL
270: + " CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) "
271: + NL
272: + " VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION, "
273: + NL
274: + " @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP) "
275: + NL + " SET @OUT_KEY = @@IDENTITY" + NL
276: + "RETURN")) {
277: // Close any results
278: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
279: }
280: s_logger.log(Level.FINEST,
281: "Procedure INSERT_BF_BLOG created.");
282:
283: ///////////////////////////////////////////////////////////////////////
284:
285: if (stmQuery
286: .execute("CREATE PROCEDURE UPDATE_BF_BLOG "
287: + NL
288: + " @IN_FOLDER VARCHAR("
289: + BLOG_FOLDER_MAXLENGTH
290: + "), "
291: + NL
292: + " @IN_CAPTION VARCHAR("
293: + BLOG_CAPTION_MAXLENGTH
294: + "), "
295: + NL
296: + " @IN_COMMENTS VARCHAR("
297: + BLOG_COMMENTS_MAXLENGTH_SYBASE
298: + "), "
299: + NL
300: + " @IN_BLOG_ID NUMERIC(10, 0), "
301: + NL
302: + " @IN_DOMAIN_ID NUMERIC(10, 0), "
303: + NL
304: + " @IN_MODIFICATION_DATE DATETIME, "
305: + NL
306: + " @OUT_TIMESTAMP DATETIME OUTPUT "
307: + NL
308: + "AS "
309: + NL
310: + " SET @OUT_TIMESTAMP = GETDATE()"
311: + NL
312: + " UPDATE "
313: + strUserName
314: + ".BF_BLOG SET FOLDER = @IN_FOLDER, "
315: + NL
316: + " CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS, "
317: + NL
318: + " MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_BLOG_ID "
319: + NL
320: + " AND DOMAIN_ID = @IN_DOMAIN_ID "
321: + NL
322: + " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE "
323: + NL + "RETURN")) {
324: // Close any results
325: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
326: }
327: s_logger.log(Level.FINEST,
328: "Procedure UPDATE_BF_BLOG created.");
329:
330: ///////////////////////////////////////////////////////////////////////
331:
332: if (stmQuery.execute("create table BF_BLOG_ENTRY" + NL
333: + "(" + NL
334: + " ID NUMERIC(10, 0) IDENTITY NOT NULL," + NL
335: + " DOMAIN_ID NUMERIC(10, 0) NOT NULL," + NL
336: + " BLOG_ID NUMERIC(10, 0) NOT NULL," + NL
337: + " CAPTION VARCHAR("
338: + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL
339: + " COMMENTS VARCHAR("
340: + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE
341: + ") NOT NULL," + NL + " IMAGEURL VARCHAR("
342: + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL
343: + " TARGETURL VARCHAR("
344: + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL,"
345: + NL + " CREATION_DATE DATETIME NOT NULL," + NL
346: + " MODIFICATION_DATE DATETIME NOT NULL,"
347: + NL
348: + " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),"
349: + NL
350: +
351: // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
352: // " REFERENCES BF_DOMAIN (ID)," + NL +
353: " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)"
354: + NL + " REFERENCES BF_BLOG (ID)" + NL + ")")) {
355: // Close any results
356: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
357: }
358: s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
359:
360: ///////////////////////////////////////////////////////////////////////
361:
362: if (stmQuery
363: .execute("CREATE PROCEDURE INSERT_BF_BLOGENTR"
364: + NL
365: + " @IN_DOMAIN_ID NUMERIC(10, 0),"
366: + NL
367: + " @IN_BLOG_ID NUMERIC(10, 0),"
368: + NL
369: + " @IN_CAPTION VARCHAR("
370: + BLOGENTRY_CAPTION_MAXLENGTH
371: + "),"
372: + NL
373: + " @IN_COMMENTS VARCHAR("
374: + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE
375: + "),"
376: + NL
377: + " @IN_IMAGEURL VARCHAR("
378: + BLOGENTRY_IMAGEURL_MAXLENGTH
379: + "),"
380: + NL
381: + " @IN_TARGETURL VARCHAR("
382: + BLOGENTRY_TARGETURL_MAXLENGTH
383: + "),"
384: + NL
385: + " @OUT_KEY INTEGER OUTPUT,"
386: + NL
387: + " @OUT_TIMESTAMP DATETIME OUTPUT "
388: + NL
389: + "AS"
390: + NL
391: + " SET @OUT_TIMESTAMP = GETDATE() "
392: + NL
393: + " INSERT INTO "
394: + strUserName
395: + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, "
396: + NL
397: + " CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)"
398: + NL
399: + " VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION, @IN_COMMENTS, "
400: + NL
401: + " @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP)"
402: + NL + " SET @OUT_KEY = @@IDENTITY" + NL
403: + "RETURN")) {
404: // Close any results
405: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
406: }
407: s_logger.log(Level.FINEST,
408: "Table INSERT_BF_BLOGENTR created.");
409:
410: ///////////////////////////////////////////////////////////////////////
411:
412: if (stmQuery
413: .execute("CREATE PROCEDURE UPDATE_BF_BLOGENTR"
414: + NL
415: + " @IN_CAPTION VARCHAR("
416: + BLOGENTRY_CAPTION_MAXLENGTH
417: + "),"
418: + NL
419: + " @IN_COMMENTS VARCHAR("
420: + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE
421: + "),"
422: + NL
423: + " @IN_IMAGEURL VARCHAR("
424: + BLOGENTRY_IMAGEURL_MAXLENGTH
425: + "),"
426: + NL
427: + " @IN_TARGETURL VARCHAR("
428: + BLOGENTRY_TARGETURL_MAXLENGTH
429: + "),"
430: + NL
431: + " @IN_ENTRY_ID NUMERIC(10, 0),"
432: + NL
433: + " @IN_DOMAIN_ID NUMERIC(10, 0),"
434: + NL
435: + " @IN_MODIFICATION_DATE DATETIME,"
436: + NL
437: + " @OUT_TIMESTAMP DATETIME OUTPUT "
438: + NL
439: + "AS "
440: + NL
441: + " SET @OUT_TIMESTAMP = GETDATE()"
442: + NL
443: + " UPDATE "
444: + strUserName
445: + ".BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION,"
446: + NL
447: + " COMMENTS = @IN_COMMENTS, IMAGEURL = @IN_IMAGEURL,"
448: + NL
449: + " TARGETURL= @IN_TARGETURL, MODIFICATION_DATE = @OUT_TIMESTAMP "
450: + NL
451: + " WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID "
452: + NL
453: + " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE"
454: + NL + "RETURN")) {
455: // Close any results
456: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
457: }
458: s_logger.log(Level.FINEST,
459: "Table UPDATE_BF_BLOGENTR created.");
460: } catch (SQLException sqleExc) {
461: s_logger.log(Level.WARNING, "Failed to create schema "
462: + BLOG_SCHEMA_NAME, sqleExc);
463: throw sqleExc;
464: } finally {
465: DatabaseUtils.closeStatement(stmQuery);
466: }
467: }
468:
469: /**
470: * {@inheritDoc}
471: */
472: public String getInsertBlogAndFetchGeneratedValues()
473: throws OSSException {
474: return "{call INSERT_BF_BLOG (?, ?, ?, ?, ?, ?)}";
475: }
476:
477: /**
478: * {@inheritDoc}
479: */
480: public String getUpdateBlogAndFetchGeneratedValues()
481: throws OSSException {
482: return "{call UPDATE_BF_BLOG ?, ?, ?, ?, ?, ?, ?)}";
483: }
484:
485: /**
486: * {@inheritDoc}
487: */
488: public String getInsertEntryAndFetchGeneratedValues()
489: throws OSSException {
490: return "{call INSERT_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?)}";
491: }
492:
493: /**
494: * {@inheritDoc}
495: */
496: public String getUpdateEntryAndFetchGeneratedValues()
497: throws OSSException {
498: return "{call UPDATE_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?)}";
499: }
500:
501: /**
502: * {@inheritDoc}
503: */
504: public int deleteRelatedData(Connection dbConnection,
505: int iDataType, int iId) throws OSSException, SQLException {
506: PreparedStatement pstmDelete = null;
507: int iDeleted = 0;
508:
509: if (iDataType == DataConstant.BLOG_DATA_TYPE) {
510: try {
511: // execute query for delete related data
512: pstmDelete = dbConnection
513: .prepareStatement("delete from BF_BLOG_ENTRY where DOMAIN_ID = ? and BLOG_ID = ?");
514: pstmDelete.setInt(1, CallContext.getInstance()
515: .getCurrentDomainId());
516: pstmDelete.setInt(2, iId);
517: iDeleted = pstmDelete.executeUpdate();
518: } finally {
519: DatabaseUtils.closeStatement(pstmDelete);
520: }
521: }
522:
523: return iDeleted;
524: }
525: }
|