package org.apache.ojb.broker.util.sequence;

 /* Copyright 2003-2005 The Apache Software Foundation

  *

  * Licensed under the Apache License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  *

  *     http://www.apache.org/licenses/LICENSE-2.0

  *

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  */

 import org.apache.commons.lang.SystemUtils;

 import org.apache.ojb.broker.PersistenceBroker;

 import org.apache.ojb.broker.platforms.PlatformException;

 import org.apache.ojb.broker.accesslayer.LookupException;

 import org.apache.ojb.broker.metadata.ClassDescriptor;

 import org.apache.ojb.broker.metadata.FieldDescriptor;

 import org.apache.ojb.broker.query.Query;

 import org.apache.ojb.broker.util.logging.Logger;

 import org.apache.ojb.broker.util.logging.LoggerFactory;

 import java.sql.CallableStatement;

 import java.sql.SQLException;

 import java.sql.Statement;

 import java.sql.Connection;

 /**

  * This solution will give those seeking an oracle-style

  * sequence generator a final answer (Identity columns really suck).

  * <br/>

  * The <code>SequenceManagerStoredProcedureImpl</code> implementation enabled database

  * sequence key generation for all databases (e.g. MSSQL, MySQL, DB2, ...)

  * with a <b>JDBC 2.0</b> compliant driver.

  * <br/>

  * First add a new table <code>OJB_NEXTVAL_SEQ</code> to

  * your database.

  * <pre>

  * CREATE TABLE OJB_NEXTVAL_SEQ

  * (

  *     SEQ_NAME    VARCHAR(150) NOT NULL,

  *     MAX_KEY     BIGINT,

  *     CONSTRAINT SYS_PK_OJB_NEXTVAL_SEQ PRIMARY KEY(SEQ_NAME)

  * )

  * </pre>

  * You will also need the stored procedure OJB_NEXTVAL

  * will will take care of giving you a guaranteed unique

  * sequence number, in multi server environments.

  * <br/>

  * <pre>

  * CREATE PROCEDURE ojb_nextval_proc @SEQ_NAME varchar(100)

  *              AS

  *                declare @MAX_KEY BIGINT

  *              -- return an error if sequence does not exist

  *              -- so we will know if someone truncates the table

  *              set @MAX_KEY = 0

  *

  *              UPDATE OJB_NEXTVAL_SEQ

  *              SET    @MAX_KEY = MAX_KEY = MAX_KEY + 1

  *              WHERE  SEQ_NAME = @SEQ_NAME

  *

  *                if @MAX_KEY = 0

  *                        select 1/0

  *                else

  *                        select @MAX_KEY

  *

  *              RETURN @MAX_KEY

  * </pre>

  * <br/>

  * It is possible to define a <code>sequence-name</code>

  * field-descriptor attribute in the repository file. If

  * such an attribute was not found, the implementation build

  * an extent aware sequence name by its own.

  * <br/>

  * Keep in mind when define a sequence name, that you are responsible

  * to be aware of extents, that is: if you ask for an uid for an

  * interface with several

  * implementor classes, or a baseclass with several subclasses the returned

  * uid have to be unique accross all tables representing objects of the

  * extent in question. Thus you have to use the same <code>sequence-name</code>

  * for all extents.

  *

  * <p>

  * Implementation configuration properties:

  * </p>

  *

  * <table cellspacing="2" cellpadding="2" border="3" frame="box">

  * <tr>

  *     <td><strong>Property Key</strong></td>

  *     <td><strong>Property Values</strong></td>

  * </tr>

  * <tr>

  *     <td>autoNaming</td>

  *     <td>

  *          Default was 'true'. If set 'true' OJB try to build a

  *          sequence name automatic if none found in field-descriptor

  *          and set this generated name as <code>sequence-name</code>

  *          in field-descriptor. If set 'false' OJB throws an exception

  *          if none sequence name was found in field-descriptor.

  *    </td>

  * </tr>

  * </table>

  *

  * <p>

  * <b>Limitations:</b>

  * <ul>

  *        <li>do not use when other application use the native key generation ditto</li>

  * </ul>

  * </p>

  * <br/>

  * <br/>

  *

  * @author Ryan Vanderwerf

  * @author Edson Carlos Ericksson Richter

  * @author Rajeev Kaul

  * @author Thomas Mahler

  * @author Armin Waibel

  * @version $Id: SequenceManagerStoredProcedureImpl.java,v 1.1 2007-08-24 22:17:29 ewestfal Exp $

  */

 public class SequenceManagerStoredProcedureImpl extends AbstractSequenceManager

 {

     private Logger log = LoggerFactory.getLogger(SequenceManagerStoredProcedureImpl.class);

     protected static final String PROCEDURE_NAME = "ojb_nextval_proc";

     protected static final String SEQ_NAME_STRING = "SEQ_NAME";

     protected static final String SEQ_ID_STRING = "MAX_KEY";

     protected static final String SEQ_TABLE_NAME = "OJB_NEXTVAL_SEQ";

     /**

      * Constructor

      * @param broker

      */

     public SequenceManagerStoredProcedureImpl(PersistenceBroker broker)

     {

         super(broker);

     }

     /**

      * Insert syntax for our special table

      * @param sequenceName

      * @param maxKey

      * @return sequence insert statement

      */

     protected String sp_createSequenceQuery(String sequenceName, long maxKey)

     {

         return "insert into " + SEQ_TABLE_NAME + " ("

                 + SEQ_NAME_STRING + "," + SEQ_ID_STRING +

                 ") values ('" + sequenceName + "'," + maxKey + ")";

     }

     /**

      * Gets the actual key - will create a new row with the max key of table if it

      * does not exist.

      * @param field

      * @return

      * @throws SequenceManagerException

      */

     protected long getUniqueLong(FieldDescriptor field) throws SequenceManagerException

     {

         boolean needsCommit = false;

         long result = 0;

         /*

         arminw:

         use the associated broker instance, check if broker was in tx or

         we need to commit used connection.

         */

         PersistenceBroker targetBroker = getBrokerForClass();

         if(!targetBroker.isInTransaction())

         {

             targetBroker.beginTransaction();

             needsCommit = true;

         }

         try

         {

             // lookup sequence name

             String sequenceName = calculateSequenceName(field);

             try

             {

                 result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);

                 /*

                 if 0 was returned we assume that the stored procedure

                 did not work properly.

                 */

                 if (result == 0)

                 {

                     throw new SequenceManagerException("No incremented value retrieved");

                 }

             }

             catch (Exception e)

             {

                 // maybe the sequence was not created

                 log.info("Could not grab next key, message was " + e.getMessage() +

                         " - try to write a new sequence entry to database");

                 try

                 {

                     // on create, make sure to get the max key for the table first

                     long maxKey = SequenceManagerHelper.getMaxForExtent(targetBroker, field);

                     createSequence(targetBroker, field, sequenceName, maxKey);

                 }

                 catch (Exception e1)

                 {

                     String eol = SystemUtils.LINE_SEPARATOR;

                     throw new SequenceManagerException(eol + "Could not grab next id, failed with " + eol +

                             e.getMessage() + eol + "Creation of new sequence failed with " +

                             eol + e1.getMessage() + eol, e1);

                 }

                 try

                 {

                     result = buildNextSequence(targetBroker, field.getClassDescriptor(), sequenceName);

                 }

                 catch (Exception e1)

                 {

                     throw new SequenceManagerException("Could not grab next id although a sequence seems to exist", e);

                 }

             }

         }

         finally

         {

             if(targetBroker != null && needsCommit)

             {

                 targetBroker.commitTransaction();

             }

         }

         return result;

     }

     /**

      * Calls the stored procedure stored procedure throws an

      * error if it doesn't exist.

      * @param broker

      * @param cld

      * @param sequenceName

      * @return

      * @throws LookupException

      * @throws SQLException

      */

     protected long buildNextSequence(PersistenceBroker broker, ClassDescriptor cld, String sequenceName)

             throws LookupException, SQLException, PlatformException

     {

         CallableStatement cs = null;

         try

         {

             Connection con = broker.serviceConnectionManager().getConnection();

             cs = getPlatform().prepareNextValProcedureStatement(con, PROCEDURE_NAME, sequenceName);

             cs.executeUpdate();

             return cs.getLong(1);

         }

         finally

         {

             try

             {

                 if (cs != null)

                     cs.close();

             }

             catch (SQLException ignore)

             {

                 // ignore it

             }

         }

     }

     /**

      * Creates new row in table

      * @param broker

      * @param field

      * @param sequenceName

      * @param maxKey

      * @throws Exception

      */

     protected void createSequence(PersistenceBroker broker, FieldDescriptor field,

                                   String sequenceName, long maxKey) throws Exception

     {

         Statement stmt = null;

         try

         {

             stmt = broker.serviceStatementManager().getGenericStatement(field.getClassDescriptor(), Query.NOT_SCROLLABLE);

             stmt.execute(sp_createSequenceQuery(sequenceName, maxKey));

         }

         catch (Exception e)

         {

             log.error(e);

             throw new SequenceManagerException("Could not create new row in "+SEQ_TABLE_NAME+" table - TABLENAME=" +

                     sequenceName + " field=" + field.getColumnName(), e);

         }

         finally

         {

             try

             {

                 if (stmt != null) stmt.close();

             }

             catch (SQLException sqle)

             {

                 if(log.isDebugEnabled())

                     log.debug("Threw SQLException while in createSequence and closing stmt", sqle);

                 // ignore it

             }

         }

     }

 }