1 21 22 package org.opensubsystems.blog.persist.db.mssql; 23 24 import java.sql.Connection ; 25 import java.sql.SQLException ; 26 import java.sql.Statement ; 27 import java.util.logging.Level ; 28 import java.util.logging.Logger ; 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 44 public class MSSQLBlogDatabaseSchema extends BlogDatabaseSchema 45 { 46 156 157 159 164 public static final int BLOG_COMMENTS_MAXLENGTH_MSSQL = 32000; 165 166 171 public static final int BLOGENTRY_COMMENTS_MAXLENGTH_MSSQL = 32000; 172 173 175 178 private static Logger s_logger = Log.getInstance(MSSQLBlogDatabaseSchema.class); 179 180 182 187 public MSSQLBlogDatabaseSchema( 188 ) throws OSSException 189 { 190 super(); 191 } 192 193 195 198 public void create( 199 Connection cntDBConnection, 200 String strUserName 201 ) throws SQLException 202 { 203 Statement 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_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL + 222 ")")) 223 { 224 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 226 } 227 s_logger.log(Level.FINEST, "Table BF_BLOG created."); 228 229 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 250 } 251 s_logger.log(Level.FINEST, "Procedure INSERT_BF_BLOG created."); 252 253 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 275 } 276 s_logger.log(Level.FINEST, "Procedure UPDATE_BF_BLOG created."); 277 278 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_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL + 296 " REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL + 297 ")")) 298 { 299 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 301 } 302 s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created."); 303 304 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 327 } 328 s_logger.log(Level.FINEST, "Table INSERT_BF_BLOGENTR created."); 329 330 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 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS); 353 } 354 s_logger.log(Level.FINEST, "Table UPDATE_BF_BLOGENTR created."); 355 } 356 catch (SQLException 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 371 public String getInsertBlogAndFetchGeneratedValues( 372 ) throws OSSException 373 { 374 return "EXEC INSERT_BF_BLOG ?, ?, ?, ?, ?, ?"; 375 } 376 377 380 public String getUpdateBlogAndFetchGeneratedValues( 381 ) throws OSSException 382 { 383 return "EXEC UPDATE_BF_BLOG ?, ?, ?, ?, ?, ?, ?"; 384 } 385 386 389 public String getInsertEntryAndFetchGeneratedValues( 390 ) throws OSSException 391 { 392 return "EXEC INSERT_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?"; 393 } 394 395 398 public String getUpdateEntryAndFetchGeneratedValues( 399 ) throws OSSException 400 { 401 return "EXEC UPDATE_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?"; 402 } 403 } 404 | Popular Tags |