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