db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Wed, 18 May 2005 13:38:59 GMT
Hi Satheesh,
 Sorry for both the gottchas.
 Here is the new patch with right files.
 thanks,
Mamta

 On 5/17/05, Satheesh Bandaram <satheesh@sourcery.org> wrote: 
> 
> Mamta, looks like this patch was sent to wrong alias... Should it be sent 
> to DerbyDev? :-) 
> 
> Also this patch seems to include a modification to 
> 'java/client/org/apache/derby/client/am/ResultSet.java' that I suspect 
> should not be in the patch. If so, can you remove that and resubmit? 
> 
> Satheesh
> 
> Mamta Satoor wrote:
> 
>  Hi,
>  I have another small patch for trigger test for IDENTITY_VAL_LOCAL. Can a 
> committer please commit it for me?
>   ********svn stat************
> M 
> java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
> M 
> java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
>  *****************************
>  thanks,
> Mamta
> 
>  On 5/13/05, Mamta Satoor <msatoor@gmail.com> wrote: 
> > 
> > Hi,
> >  I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so 
> > there is the crucial *connection* dependency identified.
> >  Also, I have added one more subtest to autoincrement.sql which tests 
> > the return value of this function for 2 different connections. Can someone 
> > commit the patch for me?
> >  ********svn stat************
> > M 
> > java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
> > M 
> > java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
> > *****************************
> >  thanks,
> > Mamta
> > 
> >  On 5/13/05, Daniel John Debrunner <djd@debrunners.com > wrote: 
> > > 
> > > Mamta Satoor wrote:
> > > 
> > > > The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value 
> > > that
> > > > got into generated for _any_ table with identity column using single 
> > > row
> > > > insert with values clause in the current transaction.
> > > 
> > > Except it doesn't behave like that, with respect to the *current
> > > transaction*. Derby's implementation returns the last identity value 
> > > for 
> > > a single row INSERT statement within the same connection.
> > > See the example below, and note auto commit is true.
> > > 
> > > And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM 
> > > mytable1,
> > > that will just return the same value multiple times (once per row in 
> > > the 
> > > table) and the value will be the last identity value for a single row
> > > INSERT statement within the same connection.
> > > 
> > > Dan.
> > > 
> > > ij> connect 'jdbc:derby:foo;create=true';
> > > ij> create table t (id int generated always as identity, d int); 
> > > 0 rows inserted/updated/deleted
> > > ij> insert into t(d) values(88);
> > > 1 row inserted/updated/deleted
> > > ij> values IDENTITY_VAL_LOCAL();
> > > 1
> > > -------------------------------
> > > 1
> > > 
> > > 1 row selected
> > > ij> select * from t; 
> > > ID |D
> > > -----------------------
> > > 1 |88
> > > 
> > > 1 row selected
> > > ij> values IDENTITY_VAL_LOCAL();
> > > 1
> > > -------------------------------
> > > 1
> > > 
> > > 1 row selected
> > > 
> > > 
> > 
> > 
> ------------------------------
> 
> Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
> ===================================================================
> --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql	(revision
170188)
> +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql	(working
copy)
> @@ -719,7 +719,25 @@
>  -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()
>  commit;
>  values IDENTITY_VAL_LOCAL();
> +-- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()
> +values IDENTITY_VAL_LOCAL();
>  drop table t1;
>  drop table t2;
>  
> +-- A table with identity column has an insert trigger which inserts into another table

> +-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the

> +-- statement table and not for the table that got modified by the trigger
> +create table t1 (c11 int generated always as identity (start with 101, increment by
3), c12 int);
> +create table t2 (c21 int generated always as identity (start with 201, increment by
5), c22 int);
> +create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22)
values (1);
> +values IDENTITY_VAL_LOCAL();
> +insert into t1 (c12) values (1);
> +-- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. 
> +-- It will not return 201 which got generated for t2 as a result of the trigger fire.
> +values IDENTITY_VAL_LOCAL();
> +select * from t1;
> +select * from t2;
> +drop table t1;
> +drop table t2;
>  
> +
> Index: java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
> ===================================================================
> --- java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out	(revision
170188)
> +++ java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out	(working
copy)
> @@ -1431,8 +1431,46 @@
>  1                              
>  -------------------------------
>  201                            
> +ij(CONN2)> -- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL()
> +values IDENTITY_VAL_LOCAL();
> +1                              
> +-------------------------------
> +201                            
>  ij(CONN2)> drop table t1;
>  0 rows inserted/updated/deleted
>  ij(CONN2)> drop table t2;
>  0 rows inserted/updated/deleted
> +ij(CONN2)> -- A table with identity column has an insert trigger which inserts into
another table 
> +-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the

> +-- statement table and not for the table that got modified by the trigger
> +create table t1 (c11 int generated always as identity (start with 101, increment by
3), c12 int);
> +0 rows inserted/updated/deleted
> +ij(CONN2)> create table t2 (c21 int generated always as identity (start with 201,
increment by 5), c22 int);
> +0 rows inserted/updated/deleted
> +ij(CONN2)> create trigger t1tr1 after insert on t1 for each row mode db2sql insert
into t2 (c22) values (1);
> +0 rows inserted/updated/deleted
> +ij(CONN2)> values IDENTITY_VAL_LOCAL();
> +1                              
> +-------------------------------
> +201                            
> +ij(CONN2)> insert into t1 (c12) values (1);
> +1 row inserted/updated/deleted
> +ij(CONN2)> -- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1.

> +-- It will not return 201 which got generated for t2 as a result of the trigger fire.
> +values IDENTITY_VAL_LOCAL();
> +1                              
> +-------------------------------
> +101                            
> +ij(CONN2)> select * from t1;
> +C11        |C12        
> +-----------------------
> +101        |1          
> +ij(CONN2)> select * from t2;
> +C21        |C22        
> +-----------------------
> +201        |1          
> +ij(CONN2)> drop table t1;
> +0 rows inserted/updated/deleted
> +ij(CONN2)> drop table t2;
> +0 rows inserted/updated/deleted
>  ij(CONN2)> 
> Index: java/client/org/apache/derby/client/am/ResultSet.java
> ===================================================================
> --- java/client/org/apache/derby/client/am/ResultSet.java	(revision 170188)
> +++ java/client/org/apache/derby/client/am/ResultSet.java	(working copy)
> @@ -2369,6 +2369,10 @@
>                  agent_.logWriter_.traceEntry(this, "updateRow");
>              }
>              updateRowX();
> +            //the cursor is not positioned on the updated row after updateRow.
> +            //User needs to issue ResultSet.next to reposition the ResultSet
> +            //on a valid row
> +            isValidCursorPosition_ = false;
>          }
>      }
>  
> @@ -2379,32 +2383,55 @@
>                      "row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY.");
>          }
>  
> -        // No-op if none of the columns were updated and updateRow() is called, just
return.
> +        // If no updateXXX has been called on this ResultSet object, then
> +        // updatedColumns_ will be null and hence no action required
>          if (updatedColumns_ == null) {
>              return;
>          }
>  
> -        if (preparedStatementForUpdate_ == null) {
> -            getPreparedStatementForUpdate();
> +        // updateXXX has been called on this ResultSet object, but check if it
> +        // has been called on the current row. If no column got updated on this
> +        // current row, then just return.
> +        boolean didAnyColumnGetUpdated = false;
> +        for (int i=0; i < updatedColumns_.length; i++) {
> +            if (columnUpdated_[i]) {
> +                didAnyColumnGetUpdated = true;
> +                break;
> +            }
>          }
> +        if (didAnyColumnGetUpdated == false) 
> +            return;
>  
> +        // User might not be updating all the updatable columns selected in the
> +        // select sql and hence every updateRow on the same ResultSet can be
> +        // potentially different than the previous one. Because of that, we
> +        // should get a new prepared statement to do updates every time
> +        getPreparedStatementForUpdate();
> +
>          // build the inputs array for the prepared statement for update
> +        int paramNumber = 0;
>          for (int i = 0; i < updatedColumns_.length; i++) {
>              if (resultSetMetaData_.sqlxUpdatable_[i] == 1) {
> +                // Since user may choose not to update all the columns in the
> +                // select list, check first if the column has been updated
> +                if (columnUpdated_[i] == false)
> +                    continue;
> +                paramNumber++;
> +
>                  // column is updated either if the updatedColumns_ entry is not null,
>                  // or if the updatedColumns_ entry is null, but columnUpdated_ boolean
is
>                  // set to true, which means columns is updated to a null.
>                  if (updatedColumns_[i] != null ||
>                          (updatedColumns_[i] == null && columnUpdated_[i])) {
> -                    preparedStatementForUpdate_.setInput(i + 1, updatedColumns_[i]);
> +                    preparedStatementForUpdate_.setInput(paramNumber, updatedColumns_[i]);
>                  } else {
>                      // Check if the original column is null.  Calling CrossConverters.setObject
on a null
>                      // column causes "Data Conversion" Exception.
>                      Object originalObj = getObject(i + 1);
>                      if (originalObj == null) {
> -                        preparedStatementForUpdate_.setInput(i + 1, null);
> +                        preparedStatementForUpdate_.setInput(paramNumber, null);
>                      } else {
> -                        preparedStatementForUpdate_.setInput(i + 1, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i],
originalObj));
> +                        preparedStatementForUpdate_.setInput(paramNumber, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i],
originalObj));
>                      }
>                  }
>              }
> @@ -2431,6 +2458,10 @@
>                  agent_.logWriter_.traceEntry(this, "deleteRow");
>              }
>              deleteRowX();
> +            //the cursor is not positioned on the deleted row after deleteRow.
> +            //User needs to issue ResultSet.next to reposition the ResultSet
> +            //on a valid row
> +            isValidCursorPosition_ = false;
>          }
>      }
>  
> @@ -2502,6 +2533,11 @@
>                          "row or if this ResultSet object has a concurrency of CONCUR_READ_ONLY.");
>              }
>  
> +            // if not on a valid row, then do not accept cancelRowUpdates call
> +            if (!isValidCursorPosition_) 
> +                throw new SqlException(agent_.logWriter_, "Invalid operation" +
> +                        "cancelRowUpdates at current cursor position.");
> +
>              // if updateRow() has already been called, then cancelRowUpdates should
have
>              // no effect.  updateRowCalled_ is reset to false as soon as the cursor
moves to a new row.
>              if (!updateRowCalled_) {
> @@ -3041,8 +3077,6 @@
>          boolean foundOneUpdatedColumnAlready = false;
>          String updateString = "UPDATE " + getTableName() + " SET ";
>  
> -        // The update tablename ... where current of cursorname for Cloudscape has
> -        // to provide columns as columnname1 = ?, columnname2 = ?
>          for (column = 1; column <= resultSetMetaData_.columns_; column++) {
>              if (columnUpdated_[column - 1]) {
>                  if (foundOneUpdatedColumnAlready) {
> @@ -3069,8 +3103,8 @@
>      private String buildDeleteString() throws SqlException {
>          String deleteString = "DELETE FROM ";
>  
> -        // build the update string using the server's cursor name
> -        deleteString += (getTableName() + " WHERE CURRENT OF " + getServerCursorName());
> +        // build the delete string using the server's cursor name
> +        deleteString += (getTableName() + " WHERE CURRENT OF \"" + getServerCursorName()
+ "\"");
>  
>          if (isRowsetCursor_) {
>              deleteString += " FOR ROW ? OF ROWSET";
> @@ -3084,12 +3118,15 @@
>          if (resultSetMetaData_.sqlxRdbnam_[0] != null &&
>                  !resultSetMetaData_.sqlxRdbnam_[0].equals(""))      // catalog
>          {
> -            tableName += resultSetMetaData_.sqlxRdbnam_[0].trim() + ".";
> +            tableName += "\"" + resultSetMetaData_.sqlxRdbnam_[0] + "\".";
>          }
> -        if (!resultSetMetaData_.sqlxSchema_[0].equals("")) {
> -            tableName += resultSetMetaData_.sqlxSchema_[0].trim() + ".";
> +        //dervied column like select 2 from t1, has null schema and table name
> +        if (resultSetMetaData_.sqlxSchema_[0] != null && !resultSetMetaData_.sqlxSchema_[0].equals(""))
{
> +            tableName += "\"" + resultSetMetaData_.sqlxSchema_[0] + "\".";
>          }
> -        tableName += resultSetMetaData_.sqlxBasename_[0].trim();
> +        if (resultSetMetaData_.sqlxBasename_[0] != null) {
> +            tableName += "\"" + resultSetMetaData_.sqlxBasename_[0] + "\"";
> +        }
>          return tableName;
>      }
>  
> @@ -3157,6 +3194,11 @@
>          if (resultSetMetaData_.sqlxUpdatable_ == null || resultSetMetaData_.sqlxUpdatable_[column
- 1] != 1) {
>              throw new SqlException(agent_.logWriter_, "Column not updatable");
>          }
> +
> +        //if not on a valid row, then do not accept updateXXX calls
> +        if (!isValidCursorPosition_)
> +            throw new SqlException(agent_.logWriter_, "Invalid operation to " +
> +                    "update at current cursor position");
>      }
>  
>      final void checkForValidColumnIndex(int column) throws SqlException {
>   
> 
>

Mime
View raw message