KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > blog > persist > db > mysql > MySQLBlogDatabaseSchema


1 /*
2  * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenChronicle
5  *
6  * $Id: MySQLBlogDatabaseSchema.java,v 1.7 2007/01/07 06:04:34 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.mysql;
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.persist.db.DatabaseImpl;
33 import org.opensubsystems.core.util.DatabaseUtils;
34 import org.opensubsystems.core.util.Log;
35
36 /**
37  * Database specific operations related to persistence of blogs and their
38  * entries. This class encapsulate functionality specific for MySQL.
39  *
40  * @version $Id: MySQLBlogDatabaseSchema.java,v 1.7 2007/01/07 06:04:34 bastafidli Exp $
41  * @author Julian Legeny
42  * @code.reviewer Miro Halas
43  * @code.reviewed 1.2 2006/07/13 23:44:36 jlegeny
44  */
45 public class MySQLBlogDatabaseSchema extends BlogDatabaseSchema
46 {
47    /*
48       Use autogenerated numbers for IDs using IDENTITY column.
49       Identity automatically defines primary key
50       Name all constraints to easily identify them later.
51       For all unique constraint we need to define unique indexes instead of
52       unique constrant otherwise we won't be able to indentify the violation of
53       this constraint by name.
54       DESCRIPTION column must be type of TEXT because VARCHAR type
55       can be max. 255 characters length.
56
57       CREATE TABLE BF_BLOG
58       (
59          ID INTEGER NOT NULL AUTO_INCREMENT,
60          DOMAIN_ID INTEGER NOT NULL,
61          FOLDER VARCHAR(50) NOT NULL,
62          CAPTION TEXT NOT NULL,
63          COMMENTS TEXT NOT NULL,
64          CREATION_DATE TIMESTAMP NOT NULL,
65          MODIFICATION_DATE TIMESTAMP NOT NULL,
66          CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
67          CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
68          // INDEX IND_BLOG_DOM_ID (DOMAIN_ID),
69          // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
70          // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
71       ) TYPE=INNODB
72
73       CREATE TABLE BF_BLOG_ENTRY
74       (
75          ID INTEGER NOT NULL AUTO_INCREMENT,
76          DOMAIN_ID INTEGER NOT NULL,
77          BLOG_ID INTEGER NOT NULL,
78          CAPTION TEXT NOT NULL,
79          COMMENTS TEXT NOT NULL,
80          IMAGEURL TEXT NOT NULL,
81          TARGETURL TEXT NOT NULL,
82          CREATION_DATE TIMESTAMP NOT NULL,
83          MODIFICATION_DATE TIMESTAMP NOT NULL,
84          CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
85          // INDEX IND_BLGENTR_DOM_ID (DOMAIN_ID),
86          INDEX IND_BLGENTR_BLG_ID (BLOG_ID),
87          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
88             REFERENCES BF_BLOG (ID) ON DELETE CASCADE,
89          // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
90          // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
91       ) TYPE=INNODB
92    */
93
94    // Cached values ////////////////////////////////////////////////////////////
95
96    /**
97     * Logger for this class
98     */
99    private static Logger JavaDoc s_logger = Log.getInstance(MySQLBlogDatabaseSchema.class);
100
101    // Constructors /////////////////////////////////////////////////////////////
102
103    /**
104     * Default constructor.
105     *
106     * @throws OSSException - error occured.
107     */
108    public MySQLBlogDatabaseSchema(
109    ) throws OSSException
110    {
111       super();
112    }
113
114    // Public methods ///////////////////////////////////////////////////////////
115
116    /**
117     * {@inheritDoc}
118     */
119    public void create(
120       Connection JavaDoc cntDBConnection,
121       String JavaDoc strUserName
122    ) throws SQLException JavaDoc
123    {
124       Statement JavaDoc stmQuery = null;
125       try
126       {
127          stmQuery = cntDBConnection.createStatement();
128          if (stmQuery.execute(
129             "create table BF_BLOG" + NL +
130             "(" + NL +
131             " ID INTEGER NOT NULL AUTO_INCREMENT," + NL +
132             " DOMAIN_ID INTEGER NOT NULL," + NL +
133             " FOLDER VARCHAR(50) NOT NULL," + NL +
134             " CAPTION TEXT NOT NULL," + NL +
135             " COMMENTS TEXT NOT NULL," + NL +
136             " CREATION_DATE TIMESTAMP NOT NULL," + NL +
137             " MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
138             " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL +
139             " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL +
140             // " INDEX IND_BLOG_DOM_ID (DOMAIN_ID)," + NL +
141 // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
142 // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE" + NL +
143 ") TYPE=INNODB"))
144          {
145             // Close any results
146 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
147          }
148          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
149 /*
150          if (stmQuery.execute("grant all on BF_BLOG to " + strUserName))
151          {
152             // Close any results
153             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
154          }
155          s_logger.log(Level.FINEST,
156                       "Access for table BF_BLOG set for user " + strUserName);
157 */
158
159          ///////////////////////////////////////////////////////////////////////
160
161          if (stmQuery.execute(
162             "create table BF_BLOG_ENTRY" + NL +
163             "(" + NL +
164             " ID INTEGER NOT NULL AUTO_INCREMENT," + NL +
165             " DOMAIN_ID INTEGER NOT NULL," + NL +
166             " BLOG_ID INTEGER NOT NULL," + NL +
167             " CAPTION TEXT NOT NULL," + NL +
168             " COMMENTS TEXT NOT NULL," + NL +
169             " IMAGEURL TEXT NOT NULL," + NL +
170             " TARGETURL TEXT NOT NULL," + NL +
171             " CREATION_DATE TIMESTAMP NOT NULL," + NL +
172             " MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
173             " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID)," + NL +
174             // " INDEX IND_BLGENTR_DOM_ID (DOMAIN_ID)," + NL +
175 " INDEX IND_BLGENTR_BLG_ID (BLOG_ID)," + NL +
176             " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL +
177             " REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
178             // " CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
179 // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE" + NL +
180 ") TYPE=INNODB"))
181          {
182             // Close any results
183 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
184          }
185          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
186 /*
187          if (stmQuery.execute("grant all on BF_BLOG_ENTRY to " + strUserName))
188          {
189             // Close any results
190             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
191          }
192          s_logger.log(Level.FINEST,
193                        "Access for table BF_BLOG_ENTRY set for user " + strUserName);
194 */
195       }
196       catch (SQLException JavaDoc sqleExc)
197       {
198          s_logger.log(Level.WARNING,
199                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
200          throw sqleExc;
201       }
202       finally
203       {
204          DatabaseUtils.closeStatement(stmQuery);
205       }
206    }
207
208    /**
209     * {@inheritDoc}
210     */
211    public String JavaDoc getInsertBlogAndFetchGeneratedValues(
212    ) throws OSSException
213    {
214       return getInsertBlog();
215    }
216
217    /**
218     * {@inheritDoc}
219     */
220    public String JavaDoc getUpdateBlogAndFetchGeneratedValues(
221    ) throws OSSException
222    {
223       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
224       
225       buffer.append("update BF_BLOG set FOLDER = ?, CAPTION = ?, COMMENTS = ?," +
226                     " MODIFICATION_DATE = ");
227       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
228       buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
229       
230       return buffer.toString();
231    }
232
233    /**
234     * {@inheritDoc}
235     */
236    public String JavaDoc getInsertBlog(
237    ) throws OSSException
238    {
239       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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().getCurrentTimestampFunctionCall());
246       buffer.append(",");
247       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
248       buffer.append(")");
249
250       return buffer.toString();
251    }
252
253    /**
254     * {@inheritDoc}
255     */
256    public String JavaDoc getInsertEntry(
257    ) throws OSSException
258    {
259       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
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().getCurrentTimestampFunctionCall());
266       buffer.append(",");
267       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
268       buffer.append(")");
269
270       return buffer.toString();
271    }
272
273    /**
274     * {@inheritDoc}
275     */
276    public String JavaDoc getInsertEntryAndFetchGeneratedValues(
277    ) throws OSSException
278    {
279       return getInsertEntry();
280    }
281    
282    /**
283     * {@inheritDoc}
284     */
285    public String JavaDoc getUpdateEntryAndFetchGeneratedValues(
286    ) throws OSSException
287    {
288       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
289       
290       buffer.append("update BF_BLOG_ENTRY set CAPTION = ?, COMMENTS = ?," +
291                     " IMAGEURL = ?, TARGETURL = ?, MODIFICATION_DATE = ");
292       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
293       buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
294       
295       return buffer.toString();
296    }
297 }
298
Popular Tags