001: /*
002: * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: MySQLBlogDatabaseSchema.java,v 1.2 2007/01/07 06:04:31 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.mysql;
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.persist.db.DatabaseImpl;
033: import org.opensubsystems.core.util.DatabaseUtils;
034: import org.opensubsystems.core.util.Log;
035:
036: /**
037: * Database specific operations related to persistence of blogs and their
038: * entries. This class encapsulate functionality specific for MySQL.
039: *
040: * @version $Id: MySQLBlogDatabaseSchema.java,v 1.2 2007/01/07 06:04:31 bastafidli Exp $
041: * @author Julian Legeny
042: * @code.reviewer Miro Halas
043: * @code.reviewed 1.2 2006/07/13 23:44:36 jlegeny
044: */
045: public class MySQLBlogDatabaseSchema extends BlogDatabaseSchema {
046: /*
047: Use autogenerated numbers for IDs using IDENTITY column.
048: Identity automatically defines primary key
049: Name all constraints to easily identify them later.
050: For all unique constraint we need to define unique indexes instead of
051: unique constrant otherwise we won't be able to indentify the violation of
052: this constraint by name.
053: DESCRIPTION column must be type of TEXT because VARCHAR type
054: can be max. 255 characters length.
055:
056: CREATE TABLE BF_BLOG
057: (
058: ID INTEGER NOT NULL AUTO_INCREMENT,
059: DOMAIN_ID INTEGER NOT NULL,
060: FOLDER VARCHAR(50) NOT NULL,
061: CAPTION TEXT NOT NULL,
062: COMMENTS TEXT NOT NULL,
063: CREATION_DATE TIMESTAMP NOT NULL,
064: MODIFICATION_DATE TIMESTAMP NOT NULL,
065: CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
066: CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
067: // INDEX IND_BLOG_DOM_ID (DOMAIN_ID),
068: // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
069: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
070: ) TYPE=INNODB
071:
072: CREATE TABLE BF_BLOG_ENTRY
073: (
074: ID INTEGER NOT NULL AUTO_INCREMENT,
075: DOMAIN_ID INTEGER NOT NULL,
076: BLOG_ID INTEGER NOT NULL,
077: CAPTION TEXT NOT NULL,
078: COMMENTS TEXT NOT NULL,
079: IMAGEURL TEXT NOT NULL,
080: TARGETURL TEXT NOT NULL,
081: CREATION_DATE TIMESTAMP NOT NULL,
082: MODIFICATION_DATE TIMESTAMP NOT NULL,
083: CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
084: // INDEX IND_BLGENTR_DOM_ID (DOMAIN_ID),
085: INDEX IND_BLGENTR_BLG_ID (BLOG_ID),
086: CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
087: REFERENCES BF_BLOG (ID) ON DELETE CASCADE,
088: // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
089: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
090: ) TYPE=INNODB
091: */
092:
093: // Cached values ////////////////////////////////////////////////////////////
094: /**
095: * Logger for this class
096: */
097: private static Logger s_logger = Log
098: .getInstance(MySQLBlogDatabaseSchema.class);
099:
100: // Constructors /////////////////////////////////////////////////////////////
101:
102: /**
103: * Default constructor.
104: *
105: * @throws OSSException - error occured.
106: */
107: public MySQLBlogDatabaseSchema() throws OSSException {
108: super ();
109: }
110:
111: // Public methods ///////////////////////////////////////////////////////////
112:
113: /**
114: * {@inheritDoc}
115: */
116: public void create(Connection cntDBConnection, String strUserName)
117: throws SQLException {
118: Statement stmQuery = null;
119: try {
120: stmQuery = cntDBConnection.createStatement();
121: if (stmQuery.execute("create table BF_BLOG" + NL + "(" + NL
122: + " ID INTEGER NOT NULL AUTO_INCREMENT," + NL
123: + " DOMAIN_ID INTEGER NOT NULL," + NL
124: + " FOLDER VARCHAR(50) NOT NULL," + NL
125: + " CAPTION TEXT NOT NULL," + NL
126: + " COMMENTS TEXT NOT NULL," + NL
127: + " CREATION_DATE TIMESTAMP NOT NULL," + NL
128: + " MODIFICATION_DATE TIMESTAMP NOT NULL," + NL
129: + " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL
130: + " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)"
131: + NL +
132: // " INDEX IND_BLOG_DOM_ID (DOMAIN_ID)," + NL +
133: // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
134: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE" + NL +
135: ") TYPE=INNODB")) {
136: // Close any results
137: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
138: }
139: s_logger.log(Level.FINEST, "Table BF_BLOG created.");
140: /*
141: if (stmQuery.execute("grant all on BF_BLOG to " + strUserName))
142: {
143: // Close any results
144: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
145: }
146: s_logger.log(Level.FINEST,
147: "Access for table BF_BLOG set for user " + strUserName);
148: */
149:
150: ///////////////////////////////////////////////////////////////////////
151: if (stmQuery
152: .execute("create table BF_BLOG_ENTRY"
153: + NL
154: + "("
155: + NL
156: + " ID INTEGER NOT NULL AUTO_INCREMENT,"
157: + NL
158: + " DOMAIN_ID INTEGER NOT NULL,"
159: + NL
160: + " BLOG_ID INTEGER NOT NULL,"
161: + NL
162: + " CAPTION TEXT NOT NULL,"
163: + NL
164: + " COMMENTS TEXT NOT NULL,"
165: + NL
166: + " IMAGEURL TEXT NOT NULL,"
167: + NL
168: + " TARGETURL TEXT NOT NULL,"
169: + NL
170: + " CREATION_DATE TIMESTAMP NOT NULL,"
171: + NL
172: + " MODIFICATION_DATE TIMESTAMP NOT NULL,"
173: + NL
174: + " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),"
175: + NL
176: +
177: // " INDEX IND_BLGENTR_DOM_ID (DOMAIN_ID)," + NL +
178: " INDEX IND_BLGENTR_BLG_ID (BLOG_ID),"
179: + NL
180: + " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)"
181: + NL
182: + " REFERENCES BF_BLOG (ID) ON DELETE CASCADE"
183: + NL +
184: // " CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
185: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE" + NL +
186: ") TYPE=INNODB")) {
187: // Close any results
188: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
189: }
190: s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
191: /*
192: if (stmQuery.execute("grant all on BF_BLOG_ENTRY to " + strUserName))
193: {
194: // Close any results
195: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
196: }
197: s_logger.log(Level.FINEST,
198: "Access for table BF_BLOG_ENTRY set for user " + strUserName);
199: */
200: } catch (SQLException sqleExc) {
201: s_logger.log(Level.WARNING, "Failed to create schema "
202: + BLOG_SCHEMA_NAME, sqleExc);
203: throw sqleExc;
204: } finally {
205: DatabaseUtils.closeStatement(stmQuery);
206: }
207: }
208:
209: /**
210: * {@inheritDoc}
211: */
212: public String getInsertBlogAndFetchGeneratedValues()
213: throws OSSException {
214: return getInsertBlog();
215: }
216:
217: /**
218: * {@inheritDoc}
219: */
220: public String getUpdateBlogAndFetchGeneratedValues()
221: throws OSSException {
222: StringBuffer buffer = new StringBuffer();
223:
224: buffer
225: .append("update BF_BLOG set FOLDER = ?, CAPTION = ?, COMMENTS = ?,"
226: + " MODIFICATION_DATE = ");
227: buffer.append(DatabaseImpl.getInstance()
228: .getCurrentTimestampFunctionCall());
229: buffer
230: .append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
231:
232: return buffer.toString();
233: }
234:
235: /**
236: * {@inheritDoc}
237: */
238: public String getInsertBlog() throws OSSException {
239: StringBuffer buffer = new StringBuffer();
240:
241: // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
242: buffer.append("INSERT INTO BF_BLOG(");
243: buffer.append(BLOG_COLUMNS);
244: buffer.append(") VALUES (null, ?, ?, ?, ?, ");
245: buffer.append(DatabaseImpl.getInstance()
246: .getCurrentTimestampFunctionCall());
247: buffer.append(",");
248: buffer.append(DatabaseImpl.getInstance()
249: .getCurrentTimestampFunctionCall());
250: buffer.append(")");
251:
252: return buffer.toString();
253: }
254:
255: /**
256: * {@inheritDoc}
257: */
258: public String getInsertEntry() throws OSSException {
259: StringBuffer buffer = new StringBuffer();
260:
261: // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
262: buffer.append("INSERT INTO BF_BLOG_ENTRY(");
263: buffer.append(ENTRY_COLUMNS);
264: buffer.append(") VALUES (null, ?, ?, ?, ?, ?, ?, ");
265: buffer.append(DatabaseImpl.getInstance()
266: .getCurrentTimestampFunctionCall());
267: buffer.append(",");
268: buffer.append(DatabaseImpl.getInstance()
269: .getCurrentTimestampFunctionCall());
270: buffer.append(")");
271:
272: return buffer.toString();
273: }
274:
275: /**
276: * {@inheritDoc}
277: */
278: public String getInsertEntryAndFetchGeneratedValues()
279: throws OSSException {
280: return getInsertEntry();
281: }
282:
283: /**
284: * {@inheritDoc}
285: */
286: public String getUpdateEntryAndFetchGeneratedValues()
287: throws OSSException {
288: StringBuffer buffer = new StringBuffer();
289:
290: buffer
291: .append("update BF_BLOG_ENTRY set CAPTION = ?, COMMENTS = ?,"
292: + " IMAGEURL = ?, TARGETURL = ?, MODIFICATION_DATE = ");
293: buffer.append(DatabaseImpl.getInstance()
294: .getCurrentTimestampFunctionCall());
295: buffer
296: .append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
297:
298: return buffer.toString();
299: }
300: }
|