KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > blog > persist > db > mssql > MSSQLBlogDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenChronicle
5  *
6  * $Id: MSSQLBlogDatabaseSchema.java,v 1.10 2007/02/20 01:58:06 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */

21
22 package org.opensubsystems.blog.persist.db.mssql;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.SQLException JavaDoc;
26 import java.sql.Statement JavaDoc;
27 import java.util.logging.Level JavaDoc;
28 import java.util.logging.Logger JavaDoc;
29
30 import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
31 import org.opensubsystems.core.error.OSSException;
32 import org.opensubsystems.core.util.DatabaseUtils;
33 import org.opensubsystems.core.util.Log;
34
35 /**
36  * Database specific operations related to persistence of blogs and their
37  * entries. This class encapsulate functionality specific for MS SQL Server.
38  *
39  * @version $Id: MSSQLBlogDatabaseSchema.java,v 1.10 2007/02/20 01:58:06 bastafidli Exp $
40  * @author Julian Legeny
41  * @code.reviewer Miro Halas
42  * @code.reviewed 1.1 2006/07/13 23:43:57 jlegeny
43  */

44 public class MSSQLBlogDatabaseSchema extends BlogDatabaseSchema
45 {
46    /*
47       Use autogenerated numbers for IDs using sequence
48       Name all constraints to easily identify them later.
49
50       CREATE TABLE BF_BLOG
51       (
52          ID INTEGER IDENTITY(1,1) NOT NULL,
53          DOMAIN_ID INTEGER NOT NULL,
54          FOLDER VARCHAR(50) NOT NULL,
55          CAPTION VARCHAR(1024) NOT NULL,
56          COMMENTS TEXT NOT NULL,
57          CREATION_DATE DATETIME NOT NULL,
58          MODIFICATION_DATE DATETIME NOT NULL,
59          CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
60          // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
61          // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE,
62          CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
63       )
64
65
66       CREATE PROCEDURE INSERT_BF_BLOG
67          @IN_DOMAIN_ID INTEGER,
68          @IN_FOLDER VARCHAR(50),
69          @IN_CAPTION VARCHAR(1024),
70          @IN_COMMENTS TEXT,
71          @OUT_KEY INTEGER OUTPUT,
72          @OUT_TIMESTAMP DATETIME OUTPUT
73       AS
74          SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;
75          INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER,
76             CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
77             VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION,
78             @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP);
79          SET @OUT_KEY = @@IDENTITY;
80       RETURN
81
82       
83       CREATE PROCEDURE UPDATE_BF_BLOG
84          @IN_FOLDER VARCHAR(50),
85          @IN_CAPTION VARCHAR(1024),
86          @IN_COMMENTS TEXT,
87          @IN_BLOG_ID INTEGER,
88          @IN_DOMAIN_ID INTEGER,
89          @IN_MODIFICATION_DATE DATETIME,
90          @OUT_TIMESTAMP DATETIME
91       AS
92          SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;
93          UPDATE BF_BLOG SET FOLDER = @IN_FOLDER, CAPTION = @IN_CAPTION,
94                 COMMENTS = @IN_COMMENTS, MODIFICATION_DATE = @OUT_TIMESTAMP
95                 WHERE ID = @IN_BLOG_ID AND DOMAIN_ID = @IN_DOMAIN_ID
96                 AND MODIFICATION_DATE = @IN_MODIFICATION_DATE;
97       RETURN
98
99
100       CREATE TABLE BF_BLOG_ENTRY
101       (
102          ID INTEGER IDENTITY(1,1) NOT NULL,
103          DOMAIN_ID INTEGER NOT NULL,
104          BLOG_ID INTEGER NOT NULL,
105          CAPTION VARCHAR(1024) NOT NULL,
106          COMMENTS TEXT NOT NULL,
107          IMAGEURL VARCHAR(1024) NOT NULL,
108          TARGETURL VARCHAR(1024) NOT NULL,
109          CREATION_DATE DATETIME NOT NULL,
110          MODIFICATION_DATE DATETIME NOT NULL,
111          CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
112          // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
113          // REFERENCES BF_DOMAIN (ID) ON DELETE NO ACTION,
114          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
115             REFERENCES BF_BLOG (ID) ON DELETE CASCADE
116       )
117
118
119       CREATE PROCEDURE INSERT_BF_BLOGENTR
120          @IN_DOMAIN_ID INTEGER,
121          @IN_BLOG_ID INTEGER,
122          @IN_CAPTION VARCHAR(1024),
123          @IN_COMMENTS TEXT,
124          @IN_IMAGEURL VARCHAR(1024) NOT NULL,
125          @IN_TARGETURL VARCHAR(1024) NOT NULL,
126          @OUT_KEY INTEGER OUTPUT,
127          @OUT_TIMESTAMP DATETIME OUTPUT
128       AS
129          SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;
130          INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID,
131             CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
132             VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION,
133             @IN_COMMENTS, @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP);
134          SET @OUT_KEY = @@IDENTITY;
135       RETURN
136
137
138       CREATE PROCEDURE UPDATE_BF_BLOGENTR
139          @IN_CAPTION VARCHAR(1024),
140          @IN_COMMENTS TEXT,
141          @IN_IMAGEURL VARCHAR(1024) NOT NULL,
142          @IN_TARGETURL VARCHAR(1024) NOT NULL,
143          @IN_ENTRY_ID INTEGER,
144          @IN_DOMAIN_ID INTEGER,
145          @IN_MODIFICATION_DATE DATETIME,
146          @OUT_TIMESTAMP DATETIME OUTPUT
147       AS
148          SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;
149          UPDATE BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS,
150                 IMAGEURL = @IN_IMAGEURL, TARGETURL= @IN_TARGETURL,
151                 MODIFICATION_DATE = @OUT_TIMESTAMP
152                 WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID
153                 AND MODIFICATION_DATE = @IN_MODIFICATION_DATE;
154       RETURN
155    */

156
157    // Constants ////////////////////////////////////////////////////////////////
158

159    /**
160     * Maximal length of blog comments.
161     * Restriction for TEXT data type is max. 2^31 characters for MS SQL Server 2000.
162     * We use less maximal value.
163     */

164    public static final int BLOG_COMMENTS_MAXLENGTH_MSSQL = 32000;
165    
166    /**
167     * Maximal length of blog entry comments.
168     * Restriction for TEXT data type is max. 2^31 characters for MS SQL Server 2000.
169     * We use less maximal value.
170     */

171    public static final int BLOGENTRY_COMMENTS_MAXLENGTH_MSSQL = 32000;
172
173    // Cached values ////////////////////////////////////////////////////////////
174

175    /**
176     * Logger for this class
177     */

178    private static Logger JavaDoc s_logger = Log.getInstance(MSSQLBlogDatabaseSchema.class);
179
180    // Constructors /////////////////////////////////////////////////////////////
181

182    /**
183     * Default constructor.
184     *
185     * @throws OSSException - error occured.
186     */

187    public MSSQLBlogDatabaseSchema(
188    ) throws OSSException
189    {
190       super();
191    }
192
193    // Public methods ///////////////////////////////////////////////////////////
194

195    /**
196     * {@inheritDoc}
197     */

198    public void create(
199       Connection JavaDoc cntDBConnection,
200       String JavaDoc strUserName
201    ) throws SQLException JavaDoc
202    {
203       Statement JavaDoc stmQuery = null;
204       try
205       {
206          stmQuery = cntDBConnection.createStatement();
207
208          if (stmQuery.execute(
209             "create table BF_BLOG" + NL +
210             "(" + NL +
211             " ID INTEGER IDENTITY(1,1) NOT NULL," + NL +
212             " DOMAIN_ID INTEGER NOT NULL," + NL +
213             " FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
214             " CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
215             " COMMENTS TEXT NOT NULL," + NL +
216             " CREATION_DATE DATETIME NOT NULL," + NL +
217             " MODIFICATION_DATE DATETIME NOT NULL," + NL +
218             " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL +
219             // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
220
// " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
221
" CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL +
222             ")"))
223          {
224             // Close any results
225
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
226          }
227          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
228
229          ///////////////////////////////////////////////////////////////////////
230

231          if (stmQuery.execute(
232             "CREATE PROCEDURE INSERT_BF_BLOG " + NL +
233             " @IN_DOMAIN_ID INTEGER, " + NL +
234             " @IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
235             " @IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
236             " @IN_COMMENTS TEXT, " + NL +
237             " @OUT_KEY INTEGER OUTPUT, " + NL +
238             " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
239             "AS " + NL +
240             " SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP; " + NL +
241             " INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER, " + NL +
242             " CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) " + NL +
243             " VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION, " + NL +
244             " @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP); " + NL +
245             " SET @OUT_KEY = @@IDENTITY;" + NL +
246             "RETURN"))
247          {
248             // Close any results
249
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
250          }
251          s_logger.log(Level.FINEST, "Procedure INSERT_BF_BLOG created.");
252
253          ///////////////////////////////////////////////////////////////////////
254

255          if (stmQuery.execute(
256             "CREATE PROCEDURE UPDATE_BF_BLOG " + NL +
257             " @IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
258             " @IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
259             " @IN_COMMENTS TEXT, " + NL +
260             " @IN_BLOG_ID INTEGER, " + NL +
261             " @IN_DOMAIN_ID INTEGER, " + NL +
262             " @IN_MODIFICATION_DATE DATETIME, " + NL +
263             " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
264             "AS " + NL +
265             " SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;" + NL +
266             " UPDATE " + strUserName + ".BF_BLOG SET FOLDER = @IN_FOLDER, " + NL +
267             " CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS, " + NL +
268             " MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_BLOG_ID " + NL +
269             " AND DOMAIN_ID = @IN_DOMAIN_ID " + NL +
270             " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE; " + NL +
271             "RETURN"))
272          {
273             // Close any results
274
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
275          }
276          s_logger.log(Level.FINEST, "Procedure UPDATE_BF_BLOG created.");
277
278          ///////////////////////////////////////////////////////////////////////
279

280          if (stmQuery.execute(
281             "create table BF_BLOG_ENTRY" + NL +
282             "(" + NL +
283             " ID INTEGER IDENTITY(1,1) NOT NULL," + NL +
284             " DOMAIN_ID INTEGER NOT NULL," + NL +
285             " BLOG_ID INTEGER NOT NULL," + NL +
286             " CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
287             " COMMENTS TEXT NOT NULL," + NL +
288             " IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
289             " TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
290             " CREATION_DATE DATETIME NOT NULL," + NL +
291             " MODIFICATION_DATE DATETIME NOT NULL," + NL +
292             " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID)," + NL +
293             // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
294
// " REFERENCES BF_DOMAIN (ID) ON DELETE NO ACTION," + NL +
295
" CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL +
296             " REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
297             ")"))
298          {
299             // Close any results
300
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
301          }
302          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
303          
304          ///////////////////////////////////////////////////////////////////////
305

306          if (stmQuery.execute(
307             "CREATE PROCEDURE INSERT_BF_BLOGENTR" + NL +
308             " @IN_DOMAIN_ID INTEGER," + NL +
309             " @IN_BLOG_ID INTEGER," + NL +
310             " @IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
311             " @IN_COMMENTS TEXT," + NL +
312             " @IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
313             " @IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
314             " @OUT_KEY INTEGER OUTPUT," + NL +
315             " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
316             "AS" + NL +
317             " SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP; " + NL +
318             " INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, " + NL +
319             " CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)" + NL +
320             " VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION, @IN_COMMENTS, " + NL +
321             " @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP);" + NL +
322             " SET @OUT_KEY = @@IDENTITY;" + NL +
323          "RETURN"))
324          {
325             // Close any results
326
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
327          }
328          s_logger.log(Level.FINEST, "Table INSERT_BF_BLOGENTR created.");
329          
330          ///////////////////////////////////////////////////////////////////////
331

332          if (stmQuery.execute(
333             "CREATE PROCEDURE UPDATE_BF_BLOGENTR" + NL +
334             " @IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
335             " @IN_COMMENTS TEXT," + NL +
336             " @IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
337             " @IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
338             " @IN_ENTRY_ID INTEGER," + NL +
339             " @IN_DOMAIN_ID INTEGER," + NL +
340             " @IN_MODIFICATION_DATE DATETIME," + NL +
341             " @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
342             "AS " + NL +
343             " SELECT @OUT_TIMESTAMP = CURRENT_TIMESTAMP;" + NL +
344             " UPDATE " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION," + NL +
345             " COMMENTS = @IN_COMMENTS, IMAGEURL = @IN_IMAGEURL," + NL +
346             " TARGETURL= @IN_TARGETURL, MODIFICATION_DATE = @OUT_TIMESTAMP " + NL +
347             " WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID " + NL +
348             " AND MODIFICATION_DATE = @IN_MODIFICATION_DATE;" + NL +
349             "RETURN"))
350          {
351             // Close any results
352
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
353          }
354          s_logger.log(Level.FINEST, "Table UPDATE_BF_BLOGENTR created.");
355       }
356       catch (SQLException JavaDoc sqleExc)
357       {
358          s_logger.log(Level.WARNING,
359                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
360          throw sqleExc;
361       }
362       finally
363       {
364          DatabaseUtils.closeStatement(stmQuery);
365       }
366    }
367
368    /**
369     * {@inheritDoc}
370     */

371    public String JavaDoc getInsertBlogAndFetchGeneratedValues(
372    ) throws OSSException
373    {
374       return "EXEC INSERT_BF_BLOG ?, ?, ?, ?, ?, ?";
375    }
376
377    /**
378     * {@inheritDoc}
379     */

380    public String JavaDoc getUpdateBlogAndFetchGeneratedValues(
381    ) throws OSSException
382    {
383       return "EXEC UPDATE_BF_BLOG ?, ?, ?, ?, ?, ?, ?";
384    }
385
386    /**
387     * {@inheritDoc}
388     */

389    public String JavaDoc getInsertEntryAndFetchGeneratedValues(
390    ) throws OSSException
391    {
392       return "EXEC INSERT_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?";
393    }
394
395    /**
396     * {@inheritDoc}
397     */

398    public String JavaDoc getUpdateEntryAndFetchGeneratedValues(
399    ) throws OSSException
400    {
401       return "EXEC UPDATE_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?";
402    }
403 }
404
Popular Tags