KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > objectstyle > cayenne > dba > sybase > SybasePkGenerator


1 /* ====================================================================
2  *
3  * The ObjectStyle Group Software License, version 1.1
4  * ObjectStyle Group - http://objectstyle.org/
5  *
6  * Copyright (c) 2002-2005, Andrei (Andrus) Adamchik and individual authors
7  * of the software. All rights reserved.
8  *
9  * Redistribution and use in source and binary forms, with or without
10  * modification, are permitted provided that the following conditions
11  * are met:
12  *
13  * 1. Redistributions of source code must retain the above copyright
14  * notice, this list of conditions and the following disclaimer.
15  *
16  * 2. Redistributions in binary form must reproduce the above copyright
17  * notice, this list of conditions and the following disclaimer in
18  * the documentation and/or other materials provided with the
19  * distribution.
20  *
21  * 3. The end-user documentation included with the redistribution, if any,
22  * must include the following acknowlegement:
23  * "This product includes software developed by independent contributors
24  * and hosted on ObjectStyle Group web site (http://objectstyle.org/)."
25  * Alternately, this acknowlegement may appear in the software itself,
26  * if and wherever such third-party acknowlegements normally appear.
27  *
28  * 4. The names "ObjectStyle Group" and "Cayenne" must not be used to endorse
29  * or promote products derived from this software without prior written
30  * permission. For written permission, email
31  * "andrus at objectstyle dot org".
32  *
33  * 5. Products derived from this software may not be called "ObjectStyle"
34  * or "Cayenne", nor may "ObjectStyle" or "Cayenne" appear in their
35  * names without prior written permission.
36  *
37  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
38  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
39  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
40  * DISCLAIMED. IN NO EVENT SHALL THE OBJECTSTYLE GROUP OR
41  * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
42  * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
43  * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
44  * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
45  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
46  * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
47  * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
48  * SUCH DAMAGE.
49  * ====================================================================
50  *
51  * This software consists of voluntary contributions made by many
52  * individuals and hosted on ObjectStyle Group web site. For more
53  * information on the ObjectStyle Group, please see
54  * <http://objectstyle.org/>.
55  */
56
57 package org.objectstyle.cayenne.dba.sybase;
58
59 import java.sql.CallableStatement JavaDoc;
60 import java.sql.Connection JavaDoc;
61 import java.sql.ResultSet JavaDoc;
62 import java.util.ArrayList JavaDoc;
63 import java.util.List JavaDoc;
64
65 import org.objectstyle.cayenne.CayenneRuntimeException;
66 import org.objectstyle.cayenne.access.DataNode;
67 import org.objectstyle.cayenne.dba.JdbcPkGenerator;
68 import org.objectstyle.cayenne.map.DbEntity;
69
70 /**
71  * Primary key generator implementation for Sybase. Uses a lookup table named
72  * "AUTO_PK_SUPPORT" and a stored procedure "auto_pk_for_table"
73  * to search and increment primary keys for tables.
74  *
75  * @author Andrei Adamchik
76  */
77 public class SybasePkGenerator extends JdbcPkGenerator {
78
79     /** Generates database objects to provide
80      * automatic primary key support. Method will execute the following
81      * SQL statements:
82      *
83      * <p>1. Executed only if a corresponding table does not exist in the
84      * database.</p>
85      *
86      * <pre>
87      * CREATE TABLE AUTO_PK_SUPPORT (
88      * TABLE_NAME VARCHAR(32) NOT NULL,
89      * NEXT_ID INTEGER NOT NULL
90      * )
91      * </pre>
92      *
93      * <p>2. Executed under any circumstances. </p>
94      *
95      * <pre>
96      * if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')
97      * BEGIN
98      * DROP PROCEDURE auto_pk_for_table
99      * END
100      * </pre>
101      *
102      * <p>3. Executed under any circumstances. </p>
103      * CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS
104      * BEGIN
105      * BEGIN TRANSACTION
106      * UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID + @pkbatchsize
107      * WHERE TABLE_NAME = @tname
108      *
109      * SELECT NEXT_ID from AUTO_PK_SUPPORT where NEXT_ID = @tname
110      * COMMIT
111      * END
112      * </pre>
113      *
114      * @param node node that provides access to a DataSource.
115      */
116     public void createAutoPk(DataNode node, List JavaDoc dbEntities) throws Exception JavaDoc {
117         super.createAutoPk(node, dbEntities);
118         super.runUpdate(node, safePkProcDrop());
119         super.runUpdate(node, unsafePkProcCreate());
120     }
121     
122     
123     public List JavaDoc createAutoPkStatements(List JavaDoc dbEntities) {
124         List JavaDoc list = super.createAutoPkStatements(dbEntities);
125         
126         // add stored procedure drop code
127 list.add(safePkProcDrop());
128         
129         // add stored procedure creation code
130 list.add(unsafePkProcCreate());
131         
132         return list;
133     }
134
135
136     /**
137      * Drops database objects related to automatic primary
138      * key support. Method will execute the following SQL
139      * statements:
140      *
141      * <pre>
142      * if exists (SELECT * FROM sysobjects WHERE name = 'AUTO_PK_SUPPORT')
143      * BEGIN
144      * DROP TABLE AUTO_PK_SUPPORT
145      * END
146      *
147      *
148      * if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')
149      * BEGIN
150      * DROP PROCEDURE auto_pk_for_table
151      * END
152      * </pre>
153      *
154      * @param node node that provides access to a DataSource.
155      */
156     public void dropAutoPk(DataNode node, List JavaDoc dbEntities) throws Exception JavaDoc {
157         super.runUpdate(node, safePkProcDrop());
158         super.runUpdate(node, safePkTableDrop());
159     }
160     
161     public List JavaDoc dropAutoPkStatements(List JavaDoc dbEntities) {
162         List JavaDoc list = new ArrayList JavaDoc();
163         list.add(safePkProcDrop());
164         list.add(safePkTableDrop());
165         return list;
166     }
167
168     protected int pkFromDatabase(DataNode node, DbEntity ent) throws Exception JavaDoc {
169         Connection JavaDoc connection = node.getDataSource().getConnection();
170         try {
171             CallableStatement JavaDoc statement = connection
172                     .prepareCall("{call auto_pk_for_table(?, ?)}");
173             try {
174                 statement.setString(1, ent.getName());
175                 statement.setInt(2, super.getPkCacheSize());
176
177                 // can't use "executeQuery"
178 // per http://jtds.sourceforge.net/faq.html#expectingResultSet
179 statement.execute();
180                 if (statement.getMoreResults()) {
181                     ResultSet JavaDoc rs = statement.getResultSet();
182
183                     try {
184                         if (rs.next()) {
185                             return rs.getInt(1);
186                         }
187                         else {
188                             throw new CayenneRuntimeException(
189                                     "Error generating pk for DbEntity " + ent.getName());
190                         }
191                     }
192                     finally {
193                         rs.close();
194                     }
195                 }
196                 else {
197                     throw new CayenneRuntimeException("Error generating pk for DbEntity "
198                             + ent.getName()
199                             + ", no result set from stored procedure.");
200                 }
201             }
202             finally {
203                 statement.close();
204             }
205         }
206         finally {
207             connection.close();
208         }
209     }
210
211
212     private String JavaDoc safePkTableDrop() {
213         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
214         buf
215             .append("if exists (SELECT * FROM sysobjects WHERE name = 'AUTO_PK_SUPPORT')")
216             .append(" BEGIN ")
217             .append(" DROP TABLE AUTO_PK_SUPPORT")
218             .append(" END");
219
220         return buf.toString();
221     }
222
223     private String JavaDoc unsafePkProcCreate() {
224         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
225         buf
226             .append(" CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS")
227             .append(" BEGIN")
228             .append(" BEGIN TRANSACTION")
229             .append(" UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID + @pkbatchsize")
230             .append(" WHERE TABLE_NAME = @tname")
231             .append(" SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = @tname")
232             .append(" COMMIT")
233             .append(" END");
234         return buf.toString();
235     }
236
237     private String JavaDoc safePkProcDrop() {
238         StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
239         buf
240             .append("if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')")
241             .append(" BEGIN")
242             .append(" DROP PROCEDURE auto_pk_for_table")
243             .append(" END");
244         return buf.toString();
245     }
246
247 }
Popular Tags