Source code: org/objectstyle/cayenne/dba/sybase/SybasePkGenerator.java
1 /* ====================================================================
2 *
3 * The ObjectStyle Group Software License, Version 1.0
4 *
5 * Copyright (c) 2002-2003 The ObjectStyle Group
6 * and individual authors of the software. All rights reserved.
7 *
8 * Redistribution and use in source and binary forms, with or without
9 * modification, are permitted provided that the following conditions
10 * are met:
11 *
12 * 1. Redistributions of source code must retain the above copyright
13 * notice, this list of conditions and the following disclaimer.
14 *
15 * 2. Redistributions in binary form must reproduce the above copyright
16 * notice, this list of conditions and the following disclaimer in
17 * the documentation and/or other materials provided with the
18 * distribution.
19 *
20 * 3. The end-user documentation included with the redistribution, if
21 * any, must include the following acknowlegement:
22 * "This product includes software developed by the
23 * ObjectStyle Group (http://objectstyle.org/)."
24 * Alternately, this acknowlegement may appear in the software itself,
25 * if and wherever such third-party acknowlegements normally appear.
26 *
27 * 4. The names "ObjectStyle Group" and "Cayenne"
28 * must not be used to endorse or promote products derived
29 * from this software without prior written permission. For written
30 * permission, please contact andrus@objectstyle.org.
31 *
32 * 5. Products derived from this software may not be called "ObjectStyle"
33 * nor may "ObjectStyle" appear in their names without prior written
34 * permission of the ObjectStyle Group.
35 *
36 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39 * DISCLAIMED. IN NO EVENT SHALL THE OBJECTSTYLE GROUP OR
40 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
41 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
42 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
43 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
44 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
45 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
46 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
47 * SUCH DAMAGE.
48 * ====================================================================
49 *
50 * This software consists of voluntary contributions made by many
51 * individuals on behalf of the ObjectStyle Group. For more
52 * information on the ObjectStyle Group, please see
53 * <http://objectstyle.org/>.
54 *
55 */
56
57 package org.objectstyle.cayenne.dba.sybase;
58
59 import java.sql.CallableStatement;
60 import java.sql.Connection;
61 import java.sql.ResultSet;
62 import java.util.ArrayList;
63 import java.util.List;
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 dbEntities) throws Exception {
117 super.createAutoPk(node, dbEntities);
118 super.runUpdate(node, safePkProcDrop());
119 super.runUpdate(node, unsafePkProcCreate());
120 }
121
122
123 public List createAutoPkStatements(List dbEntities) {
124 List 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 dbEntities) throws Exception {
157 super.runUpdate(node, safePkProcDrop());
158 super.runUpdate(node, safePkTableDrop());
159 }
160
161 public List dropAutoPkStatements(List dbEntities) {
162 List list = new ArrayList();
163 list.add(safePkProcDrop());
164 list.add(safePkTableDrop());
165 return list;
166 }
167
168 protected int pkFromDatabase(DataNode node, DbEntity ent) throws Exception {
169 Connection con = node.getDataSource().getConnection();
170 try {
171 CallableStatement st = con.prepareCall("{call auto_pk_for_table(?, ?)}");
172 try {
173 st.setString(1, ent.getName());
174 st.setInt(2, super.getPkCacheSize());
175 ResultSet rs = st.executeQuery();
176 try {
177 if (rs.next()) {
178 return rs.getInt(1);
179 }
180 else {
181 throw new CayenneRuntimeException(
182 "Error generating pk for DbEntity " + ent.getName());
183 }
184 }
185 finally {
186 rs.close();
187 }
188 }
189 finally {
190 st.close();
191 }
192 }
193 finally {
194 con.close();
195 }
196 }
197
198
199 private String safePkTableDrop() {
200 StringBuffer buf = new StringBuffer();
201 buf
202 .append("if exists (SELECT * FROM sysobjects WHERE name = 'AUTO_PK_SUPPORT')")
203 .append(" BEGIN ")
204 .append(" DROP TABLE AUTO_PK_SUPPORT")
205 .append(" END");
206
207 return buf.toString();
208 }
209
210 private String unsafePkProcCreate() {
211 StringBuffer buf = new StringBuffer();
212 buf
213 .append(" CREATE PROCEDURE auto_pk_for_table @tname VARCHAR(32), @pkbatchsize INT AS")
214 .append(" BEGIN")
215 .append(" BEGIN TRANSACTION")
216 .append(" UPDATE AUTO_PK_SUPPORT set NEXT_ID = NEXT_ID + @pkbatchsize")
217 .append(" WHERE TABLE_NAME = @tname")
218 .append(" SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = @tname")
219 .append(" COMMIT")
220 .append(" END");
221 return buf.toString();
222 }
223
224 private String safePkProcDrop() {
225 StringBuffer buf = new StringBuffer();
226 buf
227 .append("if exists (SELECT * FROM sysobjects WHERE name = 'auto_pk_for_table')")
228 .append(" BEGIN")
229 .append(" DROP PROCEDURE auto_pk_for_table")
230 .append(" END");
231 return buf.toString();
232 }
233
234 }