KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > oracle > toplink > essentials > platform > database > PostgreSQLPlatform


1 /*
2  * The contents of this file are subject to the terms
3  * of the Common Development and Distribution License
4  * (the "License"). You may not use this file except
5  * in compliance with the License.
6  *
7  * You can obtain a copy of the license at
8  * glassfish/bootstrap/legal/CDDLv1.0.txt or
9  * https://glassfish.dev.java.net/public/CDDLv1.0.html.
10  * See the License for the specific language governing
11  * permissions and limitations under the License.
12  *
13  * When distributing Covered Code, include this CDDL
14  * HEADER in each file and include the License file at
15  * glassfish/bootstrap/legal/CDDLv1.0.txt. If applicable,
16  * add the following below this CDDL HEADER, with the
17  * fields enclosed by brackets "[]" replaced with your
18  * own identifying information: Portions Copyright [yyyy]
19  * [name of copyright owner]
20  */
21 // Copyright (c) 1998, 2006, Oracle. All rights reserved.
22 package oracle.toplink.essentials.platform.database;
23
24 import java.io.*;
25 import java.util.*;
26 import java.math.BigDecimal JavaDoc;
27 import java.math.BigInteger JavaDoc;
28 import java.sql.PreparedStatement JavaDoc;
29 import java.sql.SQLException JavaDoc;
30 import oracle.toplink.essentials.exceptions.ValidationException;
31 import oracle.toplink.essentials.expressions.ExpressionOperator;
32 import oracle.toplink.essentials.descriptors.ClassDescriptor;
33 import oracle.toplink.essentials.internal.databaseaccess.FieldTypeDefinition;
34 import oracle.toplink.essentials.internal.expressions.FunctionExpression;
35 import oracle.toplink.essentials.internal.expressions.RelationExpression;
36 import oracle.toplink.essentials.internal.sessions.AbstractSession;
37 import oracle.toplink.essentials.internal.helper.*;
38 import oracle.toplink.essentials.queryframework.ValueReadQuery;
39 import oracle.toplink.essentials.tools.schemaframework.FieldDefinition;
40 import oracle.toplink.essentials.sequencing.Sequence;
41 import oracle.toplink.essentials.sequencing.DefaultSequence;
42 import oracle.toplink.essentials.sequencing.NativeSequence;
43 import oracle.toplink.essentials.sessions.DatabaseSession;
44
45 /**
46  * <p><b>Purpose</b>: Provides Postgres specific behaviour.
47  * <p><b>Responsibilities</b>:<ul>
48  * <li> Native SQL for Date, Time, & Timestamp.
49  * <li> Native sequencing.
50  * <li> Mapping of class types to database types for the schema framework.
51  * <li> Pessimistic locking.
52  * <li> Platform specific operators.
53  * </ul>
54  *
55  * @since OracleAS TopLink 10<i>g</i> (10.1.3)
56  */
57 public class PostgreSQLPlatform extends DatabasePlatform {
58     
59     public PostgreSQLPlatform() {
60         super();
61     }
62     
63     /**
64      * Appends a Boolean value.
65      * Refer to : http://www.postgresql.org/docs/8.0/static/datatype-boolean.html
66      * In PostgreSQL the following are the values that
67      * are value for a boolean field
68      * Valid literal values for the "true" state are:
69      * TRUE, 't', 'true', 'y', 'yes', '1'
70      * Valid literal values for the false" state are :
71      * FALSE, 'f', 'false', 'n', 'no', '0'
72      *
73      * To be consistent with the other data platforms we are using the values
74      * '1' and '0' for true and false states of a boolean field.
75      */
76     protected void appendBoolean(Boolean JavaDoc bool, Writer writer) throws IOException {
77         if (bool.booleanValue()) {
78             writer.write("\'1\'");
79         } else {
80             writer.write("\'0\'");
81         }
82     }
83     
84     /**
85      * INTERNAL:
86      * Initialize any platform-specific operators
87      */
88     protected void initializePlatformOperators() {
89         super.initializePlatformOperators();
90         addOperator(ExpressionOperator.simpleLogicalNoParens(ExpressionOperator.Concat, "||"));
91         addOperator(ExpressionOperator.simpleTwoArgumentFunction(ExpressionOperator.Nvl, "NULLIF"));
92         addOperator(operatorLocate());
93     }
94
95
96     /**
97      * INTERNAL:
98      * This method returns the query to select the timestamp from the server
99      * for Derby.
100      */
101     public ValueReadQuery getTimestampQuery() {
102         if (timestampQuery == null) {
103             timestampQuery = new ValueReadQuery();
104             timestampQuery.setSQLString("SELECT NOW()");
105         }
106         return timestampQuery;
107
108     }
109
110
111     /**
112      * This method is used to print the output parameter token when stored
113      * procedures are called
114      */
115     public String JavaDoc getInOutputProcedureToken() {
116         return "OUT";
117     }
118
119     /**
120      * This is required in the construction of the stored procedures with
121      * output parameters
122      */
123     public boolean shouldPrintOutputTokenAtStart() {
124         //TODO: Check with the reviewer where this is used
125 return false;
126     }
127
128     /**
129      * INTERNAL:
130      * Answers whether platform is Derby
131      */
132     public boolean isPostgreSQL() {
133         return true;
134     }
135
136     /**
137      * INTERNAL:
138      */
139     protected String JavaDoc getCreateTempTableSqlSuffix() {
140         // http://pgsqld.active-venture.com/sql-createtable.html
141 return " ON COMMIT PRESERVE ROWS";
142     }
143
144     public boolean supportsNativeSequenceNumbers() {
145         return true;
146     }
147
148     /**
149      * INTERNAL:
150      * Indicates whether NativeSequence should retrieve
151      * sequence value after the object has been inserted into the db
152      * This method is to be used *ONLY* by sequencing classes
153      */
154     public boolean shouldNativeSequenceAcquireValueAfterInsert() {
155         return true;
156     }
157
158
159     /**
160      * INTERNAL:
161      * Build the identity query for native sequencing.
162      */
163     public ValueReadQuery buildSelectQueryForNativeSequence(String JavaDoc seqName, Integer JavaDoc size) {
164         ValueReadQuery selectQuery = new ValueReadQuery();
165         selectQuery.setSQLString("select currval(\'" + seqName + "\')");
166         return selectQuery;
167     }
168
169
170     /**
171      * INTERNAL:
172      */
173      protected String JavaDoc getCreateTempTableSqlBodyForTable(DatabaseTable table) {
174         // returning null includes fields of the table in body
175 // see javadoc of DatabasePlatform#getCreateTempTableSqlBodyForTable(DataBaseTable)
176 // for details
177 return null;
178      }
179
180     /**
181      * INTERNAL:
182      * Append the receiver's field 'identity' constraint clause to a writer
183      */
184     public void printFieldIdentityClause(Writer writer) throws ValidationException {
185         try {
186             writer.write(" SERIAL");
187         } catch (IOException ioException) {
188             throw ValidationException.fileError(ioException);
189         }
190     }
191
192     protected Hashtable buildFieldTypes() {
193         Hashtable fieldTypeMapping = new Hashtable();
194
195         fieldTypeMapping.put(Boolean JavaDoc.class, new FieldTypeDefinition("BOOLEAN", false));
196
197         fieldTypeMapping.put(Integer JavaDoc.class, new FieldTypeDefinition("INTEGER", false));
198         fieldTypeMapping.put(Long JavaDoc.class, new FieldTypeDefinition("BIGINT", false));
199         fieldTypeMapping.put(Float JavaDoc.class, new FieldTypeDefinition("FLOAT"));
200         fieldTypeMapping.put(Double JavaDoc.class, new FieldTypeDefinition("FLOAT", false));
201         fieldTypeMapping.put(Short JavaDoc.class, new FieldTypeDefinition("SMALLINT", false));
202         fieldTypeMapping.put(Byte JavaDoc.class, new FieldTypeDefinition("SMALLINT", false));
203         fieldTypeMapping.put(java.math.BigInteger JavaDoc.class, new FieldTypeDefinition("BIGINT", false));
204         fieldTypeMapping.put(java.math.BigDecimal JavaDoc.class, new FieldTypeDefinition("DECIMAL",38));
205         fieldTypeMapping.put(Number JavaDoc.class, new FieldTypeDefinition("DECIMAL",38));
206
207         fieldTypeMapping.put(String JavaDoc.class, new FieldTypeDefinition("VARCHAR", 255));
208         fieldTypeMapping.put(Character JavaDoc.class, new FieldTypeDefinition("CHAR", 1));
209
210         fieldTypeMapping.put(Byte JavaDoc[].class, new FieldTypeDefinition("BYTEA"));
211         fieldTypeMapping.put(Character JavaDoc[].class, new FieldTypeDefinition("TEXT"));
212         fieldTypeMapping.put(byte[].class, new FieldTypeDefinition("BYTEA"));
213         fieldTypeMapping.put(char[].class, new FieldTypeDefinition("TEXT"));
214         fieldTypeMapping.put(java.sql.Blob JavaDoc.class, new FieldTypeDefinition("BYTEA"));
215         fieldTypeMapping.put(java.sql.Clob JavaDoc.class, new FieldTypeDefinition("TEXT"));
216
217         fieldTypeMapping.put(java.sql.Date JavaDoc.class, new FieldTypeDefinition("DATE", false));
218         fieldTypeMapping.put(java.sql.Time JavaDoc.class, new FieldTypeDefinition("TIME", false));
219         fieldTypeMapping.put(java.sql.Timestamp JavaDoc.class, new FieldTypeDefinition("TIMESTAMP", false));
220
221         return fieldTypeMapping;
222     }
223     
224     /**
225      * INTERNAL:
226      * Override the default locate operator
227      */
228     protected ExpressionOperator operatorLocate() {
229         ExpressionOperator result = new ExpressionOperator();
230         result.setSelector(ExpressionOperator.Locate);
231         Vector v = new Vector(3);
232         v.addElement("STRPOS(");
233         v.addElement(", ");
234         v.addElement(")");
235         result.printsAs(v);
236         result.bePrefix();
237         result.setNodeClass(RelationExpression.class);
238         return result;
239     }
240
241
242     /**
243      * INTERNAL:
244      */
245     public boolean supportsGlobalTempTables() {
246         return true;
247     }
248
249     /**
250      * INTERNAL:
251      */
252     protected String JavaDoc getCreateTempTableSqlPrefix() {
253         return "CREATE GLOBAL TEMPORARY TABLE ";
254     }
255                 
256     /**
257      * INTERNAL:
258      * returns the maximum number of characters that can be used in a field
259      * name on this platform.
260      */
261     public int getMaxFieldNameSize() {
262         // The system uses no more than NAMEDATALEN-1 characters of an identifier; longer names can be written in commands,
263 // but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 (but at the time PostgreSQL
264 // is built, NAMEDATALEN can be changed in src/include/postgres_ext.h).
265 // http://www.postgresql.org/docs/7.3/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
266 return 63;
267     }
268
269     // http://www.postgresql.org/docs/8.1/interactive/plpgsql-declarations.html
270 /**
271      * INTERNAL:
272      * Used for sp calls.
273      */
274     public String JavaDoc getProcedureBeginString() {
275         return "AS $$ BEGIN ";
276     }
277
278     /**
279      * INTERNAL:
280      * Used for sp calls.
281      */
282     public String JavaDoc getProcedureEndString() {
283         return "; END ; $$ LANGUAGE plpgsql;";
284     }
285
286     /**
287      * INTERNAL:
288      * Used for sp calls.
289      */
290     public String JavaDoc getProcedureCallHeader() {
291         return "EXECUTE ";
292     }
293     
294     /**
295      * INTERNAL
296      * Used for stored function calls.
297      */
298     public String JavaDoc getAssignmentString() {
299         return ":= ";
300     }
301     
302     public void printFieldTypeSize(Writer writer, FieldDefinition field,
303             FieldTypeDefinition fieldType, AbstractSession session, String JavaDoc qualifiedFieldName) throws IOException {
304         if(!shouldAcquireSequenceValueAfterInsert(session, qualifiedFieldName)) {
305             writer.write(fieldType.getName());
306             if ((fieldType.isSizeAllowed()) && ((field.getSize() != 0) || (fieldType.isSizeRequired()))) {
307                 writer.write("(");
308                 if (field.getSize() == 0) {
309                     writer.write(new Integer JavaDoc(fieldType.getDefaultSize()).toString());
310                 } else {
311                     writer.write(new Integer JavaDoc(field.getSize()).toString());
312                 }
313                 if (field.getSubSize() != 0) {
314                     writer.write(",");
315                     writer.write(new Integer JavaDoc(field.getSubSize()).toString());
316                 } else if (fieldType.getDefaultSubSize() != 0) {
317                     writer.write(",");
318                     writer.write(new Integer JavaDoc(fieldType.getDefaultSubSize()).toString());
319                 }
320                 writer.write(")");
321             }
322         }
323     }
324     
325     public void printFieldUnique(Writer writer, boolean isUnique,
326             AbstractSession session, String JavaDoc qualifiedFieldName) throws IOException {
327        if(!shouldAcquireSequenceValueAfterInsert(session, qualifiedFieldName)) {
328             if (isUnique) {
329                 if (supportsPrimaryKeyConstraint()) {
330                     writer.write(" UNIQUE");
331                 }
332             }
333        }
334     }
335
336     /**
337      * JDBC defines and outer join syntax, many drivers do not support this. So we normally avoid it.
338      */
339     public boolean shouldUseJDBCOuterJoinSyntax() {
340         return false;
341     }
342      
343     /**
344      * Set a primitive parameter.
345      * Postgres jdbc client driver give problem when doing a setObject() for wrapper types.
346      * Ideally this code should be in the DatabasePlatform so that all platforms can use
347      * this.
348      */
349     protected void setPrimitiveParameterValue(final PreparedStatement JavaDoc statement, final int index,
350             final Object JavaDoc parameter) throws SQLException JavaDoc {
351        if (parameter instanceof Number JavaDoc) {
352             Number JavaDoc number = (Number JavaDoc) parameter;
353             if (number instanceof Integer JavaDoc) {
354                 statement.setInt(index, number.intValue());
355             } else if (number instanceof Long JavaDoc) {
356                 statement.setLong(index, number.longValue());
357             } else if (number instanceof Short JavaDoc) {
358                 statement.setShort(index, number.shortValue());
359             } else if (number instanceof Byte JavaDoc) {
360                 statement.setByte(index, number.byteValue());
361             } else if (number instanceof Double JavaDoc) {
362                 statement.setDouble(index, number.doubleValue());
363             } else if (number instanceof Float JavaDoc) {
364                 statement.setFloat(index, number.floatValue());
365             } else if (number instanceof BigDecimal JavaDoc) {
366                 statement.setBigDecimal(index, (BigDecimal JavaDoc) number);
367             } else if (number instanceof BigInteger JavaDoc) {
368                 statement.setBigDecimal(index, new BigDecimal JavaDoc((BigInteger JavaDoc) number));
369             } else {
370                 statement.setObject(index, parameter);
371             }
372         } else if (parameter instanceof String JavaDoc) {
373             statement.setString(index, (String JavaDoc)parameter);
374         } else if (parameter instanceof Boolean JavaDoc) {
375             statement.setBoolean(index, ((Boolean JavaDoc) parameter).booleanValue());
376         } else {
377             statement.setObject(index, parameter);
378         }
379     }
380
381     protected String JavaDoc getDBSequenceName(String JavaDoc tableName, String JavaDoc pkFieldName) {
382         StringBuffer JavaDoc seqName = new StringBuffer JavaDoc(tableName);
383         seqName.append("_").append(pkFieldName).append("_seq");
384         return seqName.toString();
385     }
386     
387      /**
388      * INTERNAL:
389      * Platform specific sequencing initialization.
390      * This internal method should only be called by SequencingManager.
391      *
392      * For PostgreSQL database for id fields that are defined as "SERIAL" the database creates
393      * an implicit sequence. The name of the sequence provided by the database is of the form
394      * <table_name>_<id_field_name>_seq.
395      * As part of the insert statement we need to get the correct sequence to be able to get
396      * the next value. This code ensures that we have our internal structures pointing to the
397      * correct sequence name for the runtime code to work correctly.
398      * The current version of postgresql allows only 1 SERIAL field to be defined per table.
399      */
400     public void platformSpecificSequencingInitialization(DatabaseSession session) {
401         Boolean JavaDoc isDefaultSequenceNative = null;
402         
403         // We need to iterate over all the entities
404 Iterator iterator = session.getProject().getDescriptors().values().iterator();
405         Sequence sequence;
406         
407         while (iterator.hasNext()) {
408             ClassDescriptor descriptor = (ClassDescriptor)iterator.next();
409
410             // Ensure that the entity uses sequencing
411 if (descriptor.usesSequenceNumbers()) {
412                 String JavaDoc currSequenceName = descriptor.getSequenceNumberName();
413                 sequence = (Sequence)getSequences().get(currSequenceName);
414
415                 boolean shouldVerifySequenceName;
416
417                 boolean usesDefaultSequence = sequence == null || sequence instanceof DefaultSequence;
418                 if(usesDefaultSequence) {
419                     // is defaultSequence is a NativeSequence?
420 if(isDefaultSequenceNative == null) {
421                         isDefaultSequenceNative = new Boolean JavaDoc(getDefaultSequence() instanceof NativeSequence);
422                     }
423                     shouldVerifySequenceName = isDefaultSequenceNative.booleanValue();
424                 } else {
425                     shouldVerifySequenceName = sequence instanceof NativeSequence;
426                 }
427
428                 if(shouldVerifySequenceName) {
429                     DatabaseTable tbl = (DatabaseTable)descriptor.getTables().firstElement();
430                     String JavaDoc tableName = tbl.getQualifiedName();
431                     String JavaDoc pkFieldName = descriptor.getSequenceNumberField().getName();
432                     String JavaDoc seqName = getDBSequenceName(tableName, pkFieldName);
433                     if(!currSequenceName.equals(seqName)) {
434                         descriptor.setSequenceNumberName(seqName);
435                         if(sequence != null) {
436                             removeSequence(currSequenceName);
437                             sequence.setName(seqName);
438                             addSequence(sequence);
439                         }
440                     }
441                 }
442             }
443         }
444     }
445    
446 }
447
Popular Tags