Re: [Hsqldb-developers] isolation support in HSQL 1.8.0 version

[wim den ouden <wdenouden@gm...>]

hi Irum
did you see http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200501.mbox/%3C446591124.1106594315052.JavaMail.jira@...%3E
it's about hsql isolation (and derby)
gr
wim
search on google with hsqldb isolation


On Wed, 30 Mar 2005 12:24:14 -0800 (PST), Irum Godil
<softwarengineer2004@...> wrote:
> Hi, 
>   
> I downloaded HSQL version 1.8.0 from the website and wrote a small test that
> has 2 connection variables to the same database. I wanted to see how is
> locking for isolation support working. 
>   
> Both connections update the same row of the database. Unfortunately, this is
> allowed in the system, and I would expect that the second connections will
> not be able to do so, given the locking support. Following is my test and
> its results. Please see the bolded red lines where I am updating the 2 rows.
>   
> Also, when I run the test in version 1.7.3 I get the same results. Can
> someone please tell me if I have written something wrong, or if my locking
> understanding is incorrect. I will really appreciate your response. 
>   
> 
> public class IsolationIrumTest { 
> 
> 
>     Connection conn1;
>     Connection conn2;
>     //our connnection to the db - presist for life of program 
> 
>     // we dont want this garbage collected until we are done
>     public IsolationIrumTest(String db_file_name_prefix) throws Exception { 
>   // note more general exception 
> 
>         // Load the HSQL Database Engine JDBC driver
>         // hsqldb.jar should be in the class path or made part of the
> current jar
>         Class.forName("org.hsqldb.jdbcDriver"); 
> 
>        conn1 = DriverManager.getConnection("jdbc:hsqldb:"
>                                            + db_file_name_prefix,    //
> filenames
>                                            "sa",                     //
> username
>                                            "");           
>         
>         conn2 = DriverManager.getConnection("jdbc:hsqldb:"
>                 + db_file_name_prefix,    // filenames
>                 "sa",                     // username
>                 "");       
>         conn1.setAutoCommit(false);
>         conn2.setAutoCommit(false);
>     } 
> 
>     public void shutdown() throws SQLException { 
> 
>      update1("SHUTDOWN");
>    
>      
>         conn1.close();    // if there are no other open connection
>         conn2.close();
> //        
>         
>        
>     }
>     public synchronized void query1(String expression) throws SQLException {
> 
>         Statement st = null;
>         ResultSet rs = null; 
> 
>         st = conn1.createStatement();         // statement objects can be
> reused with 
> 
>         // repeated calls to execute but we
>         // choose to make a new one each time
>         rs = st.executeQuery(expression);    // run the query 
> 
>         // do something with the result set.
>         dump(rs);
>         st.close();    // NOTE!! if you close a statement the associated
> ResultSet is 
> 
>         // closed too
>         // so you should copy the contents to some other object.
>         // the result set is invalidated also  if you recycle an Statement
>         // and try to execute some other query before the result set has
> been
>         // completely examined.
>     } 
> 
>     public synchronized void query2(String expression) throws SQLException {
> 
>         Statement st = null;
>         ResultSet rs = null; 
> 
>         st = conn2.createStatement();         // statement objects can be
> reused with 
> 
>         // repeated calls to execute but we
>         // choose to make a new one each time
>         rs = st.executeQuery(expression);    // run the query 
> 
>         // do something with the result set.
>         dump(rs);
>         st.close();    // NOTE!! if you close a statement the associated
> ResultSet is 
> 
>         
>     } 
> 
>     
> 
>     public synchronized void update1(String expression) throws SQLException
> { 
> 
>         Statement st = null; 
> 
>         st = conn1.createStatement();    // statements 
> 
>         int i = st.executeUpdate(expression);    // run the query 
> 
>         if (i == -1) {
>             System.out.println("db error : " + expression);
>         } 
> 
>         st.close();
>     }    // void update() 
> 
>     
>     
>     public synchronized void update2(String expression) throws SQLException
> { 
> 
>         Statement st = null; 
> 
>         st = conn2.createStatement();    // statements 
> 
>         int i = st.executeUpdate(expression);    // run the query 
> 
>         if (i == -1) {
>             System.out.println("db error : " + expression);
>         } 
> 
>         st.close();
>     }    // void update() 
> 
>     
>     
>     
>     public static void dump(ResultSet rs) throws SQLException { 
> 
>         // the order of the rows in a cursor
>         // are implementation dependent unless you use the SQL ORDER
> statement
>         ResultSetMetaData meta   = rs.getMetaData();
>         int               colmax = meta.getColumnCount();
>         int               i;
>         Object            o = null; 
> 
>         // the result set is a cursor into the data.  You can only
>         // point to one row at a time
>         // assume we are pointing to BEFORE the first row
>         // rs.next() points to next row and returns true
>         // or false if there is no next row, which breaks the loop
>         for (; rs.next(); ) {
>             for (i = 0; i < colmax; ++i) {
>                 o = rs.getObject(i + 1);    // Is SQL the first column is
> indexed 
> 
>                 // with 1 not 0
>                 System.out.print(o.toString() + " ");
>             } 
> 
>             System.out.println(" ");
>         }
>     }                                       //void dump( ResultSet rs ) 
> 
>     public static void main(String[] args) { 
> 
>      IsolationIrumTest db = null; 
> 
>         try {
>             db = new IsolationIrumTest("db_file");
>         } catch (Exception ex1) {
>             ex1.printStackTrace();    // could not start db 
> 
>             return;                   // bye bye
>         } 
> 
>         try { 
> 
>          //FROM CONNECTION 1 
>             db.update1(
>                 "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col
> VARCHAR(256), num_col INTEGER, num_col2 INTEGER)");
>         } catch (SQLException ex2) { 
> 
>             //ignore
>             //ex2.printStackTrace();  // second time we run program
>             //  should throw execption since table
>             // already there
>             //
>             // this will have no effect on the db
>         } 
> 
>         try { 
> 
>             // add some rows - will create duplicates if run more then once
>             // the id column is automatically generated
>             db.update1(
>                 "INSERT INTO sample_table(str_col,num_col, num_col2)
> VALUES('Ford', 100, 50)");
>             
>             // do a query
>             db.query1("SELECT * FROM sample_table");
>             
>             
>             
> 
>             
>             //NOW UPDATE THROUGH CONNECTION1
>             db.update1("UPDATE sample_table SET num_col = '34' WHERE str_col
> = 'Ford'"); 
> 
>               System.out.println("From connection1: ");
> 
>             db.query1("SELECT * FROM sample_table");
>             
>             //now update throught connection2.
>             db.update2("UPDATE sample_table SET num_col = '36' WHERE str_col
> = 'Ford'");
>             
>             
>             System.out.println("From connection2: ");
>             db.query2("SELECT * FROM sample_table");           
>                                   
>             
>             // at end of program
>             db.shutdown();
>         } catch (SQLException ex3) {
>             ex3.printStackTrace();
>         }
>     }    // main()
> }    // class Testdb
>  
> 
>   
> 
> My Test Results are: 
> 
> 0 Ford 100 50 
> 
> From connection1: 
> 
> 0 Ford 34 50 
> 
> From connection2: 
> 
> 0 Ford 36 50 
> 
>   
> Thanks a lot. 
> Sincerely, 
> Irum Godil.
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com

Re: [Hsqldb-developers] isolation supported

[fredt <fredt@us...>]

Hi Irum,

You have lots of questions all the thime :).=20

1) This code has not been released. By the time it does, it will allow =
only some isolation levels. In its current form it does not support more =
than one level.

2) The locks are on modified rows not predicates. Don't be too concerned =
if you read something else in your book -- you may or may not be reading =
it in the right context.

Fred
  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsql=20
  Sent: 31 March 2005 06:08
  Subject: [Hsqldb-developers] isolation supported


  Hi,=20
  Thanks, I was able to set up the project and get errors in wrong =
cases. As I am trying to study the isolation supported, I have 2 =
questions as follows:=20

  1) In the class jdbcConnection, in the method =
setTransactionIsolation(level), the implementation allows to set any of =
the 4 Isolation levels. But, does not the system only support =
READ_UNCOMMITTED level; or does it support any of the 4 levels?

  2) Also, when locking on write, I understand that a specific row being =
updated by a Transaction is locked right? The lock is not on the =
predicates right? I am concerned because the book I was reading on =
Transactions said that all Isolation Levels do write locks on =
predicates; but from my understanding of the implementation only locks =
are made on the tuples.=20

  Thanks a lot.=20
  Sincerely,
  Irum Godil.


-------------------------------------------------------------------------=
-----
  Do you Yahoo!?
  Yahoo! Small Business - Try our new resources site! 

[Hsqldb-developers] isolation supported

[Irum Godil <softwarengineer2004@ya...>]

Hi, 
Thanks, I was able to set up the project and get errors in wrong cases. As I am trying to study the isolation supported, I have 2 questions as follows: 
 
1) In the class jdbcConnection, in the method setTransactionIsolation(level), the implementation allows to set any of the 4 Isolation levels. But, does not the system only support READ_UNCOMMITTED level; or does it support any of the 4 levels?
 
2) Also, when locking on write, I understand that a specific row being updated by a Transaction is locked right? The lock is not on the predicates right? I am concerned because the book I was reading on Transactions said that all Isolation Levels do write locks on predicates; but from my understanding of the implementation only locks are made on the tuples. 
 
Thanks a lot. 
Sincerely,
Irum Godil.

		
---------------------------------
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: [Hsqldb-developers] isolation support in HSQL 1.8.0 version

[fredt <fredt@us...>]

You should use the hsqldb-dev CVS version for this.

Fred
  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsql=20
  Sent: 30 March 2005 21:24
  Subject: [Hsqldb-developers] isolation support in HSQL 1.8.0 version


  Hi,

  I downloaded HSQL version 1.8.0 from the website and wrote a small =
test that has 2 connection variables to the same database. I wanted to =
see how is locking for isolation support working.

  Both connections update the same row of the database. Unfortunately, =
this is allowed in the system, and I would expect that the second =
connections will not be able to do so, given the locking support. =
Following is my test and its results. Please see the bolded red lines =
where I am updating the 2 rows.=20

  Also, when I run the test in version 1.7.3 I get the same results. Can =
someone please tell me if I have written something wrong, or if my =
locking understanding is incorrect. I will really appreciate your =
response.

  public class IsolationIrumTest {


      Connection conn1;
      Connection conn2;
      //our connnection to the db - presist for life of program

      // we dont want this garbage collected until we are done
      public IsolationIrumTest(String db_file_name_prefix) throws =
Exception {    // note more general exception

          // Load the HSQL Database Engine JDBC driver
          // hsqldb.jar should be in the class path or made part of the =
current jar
          Class.forName("org.hsqldb.jdbcDriver");

         conn1 =3D DriverManager.getConnection("jdbc:hsqldb:"
                                             + db_file_name_prefix,    =
// filenames
                                             "sa",                     =
// username
                                             "");          =20
         =20
          conn2 =3D DriverManager.getConnection("jdbc:hsqldb:"
                  + db_file_name_prefix,    // filenames
                  "sa",                     // username
                  "");      =20
          conn1.setAutoCommit(false);
          conn2.setAutoCommit(false);
      }

      public void shutdown() throws SQLException {

       update1("SHUTDOWN");
    =20
      =20
          conn1.close();    // if there are no other open connection
          conn2.close();
  //       =20
         =20
        =20
      }
      public synchronized void query1(String expression) throws =
SQLException {

          Statement st =3D null;
          ResultSet rs =3D null;

          st =3D conn1.createStatement();         // statement objects =
can be reused with

          // repeated calls to execute but we
          // choose to make a new one each time
          rs =3D st.executeQuery(expression);    // run the query

          // do something with the result set.
          dump(rs);
          st.close();    // NOTE!! if you close a statement the =
associated ResultSet is

          // closed too
          // so you should copy the contents to some other object.
          // the result set is invalidated also  if you recycle an =
Statement
          // and try to execute some other query before the result set =
has been
          // completely examined.
      }

      public synchronized void query2(String expression) throws =
SQLException {

          Statement st =3D null;
          ResultSet rs =3D null;

          st =3D conn2.createStatement();         // statement objects =
can be reused with

          // repeated calls to execute but we
          // choose to make a new one each time
          rs =3D st.executeQuery(expression);    // run the query

          // do something with the result set.
          dump(rs);
          st.close();    // NOTE!! if you close a statement the =
associated ResultSet is

         =20
      }

     =20

      public synchronized void update1(String expression) throws =
SQLException {

          Statement st =3D null;

          st =3D conn1.createStatement();    // statements

          int i =3D st.executeUpdate(expression);    // run the query

          if (i =3D=3D -1) {
              System.out.println("db error : " + expression);
          }

          st.close();
      }    // void update()

     =20
     =20
      public synchronized void update2(String expression) throws =
SQLException {

          Statement st =3D null;

          st =3D conn2.createStatement();    // statements

          int i =3D st.executeUpdate(expression);    // run the query

          if (i =3D=3D -1) {
              System.out.println("db error : " + expression);
          }

          st.close();
      }    // void update()

     =20
     =20
     =20
      public static void dump(ResultSet rs) throws SQLException {

          // the order of the rows in a cursor
          // are implementation dependent unless you use the SQL ORDER =
statement
          ResultSetMetaData meta   =3D rs.getMetaData();
          int               colmax =3D meta.getColumnCount();
          int               i;
          Object            o =3D null;

          // the result set is a cursor into the data.  You can only
          // point to one row at a time
          // assume we are pointing to BEFORE the first row
          // rs.next() points to next row and returns true
          // or false if there is no next row, which breaks the loop
          for (; rs.next(); ) {
              for (i =3D 0; i < colmax; ++i) {
                  o =3D rs.getObject(i + 1);    // Is SQL the first =
column is indexed

                  // with 1 not 0
                  System.out.print(o.toString() + " ");
              }

              System.out.println(" ");
          }
      }                                       //void dump( ResultSet rs =
)

      public static void main(String[] args) {

       IsolationIrumTest db =3D null;

          try {
              db =3D new IsolationIrumTest("db_file");
          } catch (Exception ex1) {
              ex1.printStackTrace();    // could not start db

              return;                   // bye bye
          }

          try {

           //FROM CONNECTION 1=20
              db.update1(
                  "CREATE TABLE sample_table ( id INTEGER IDENTITY, =
str_col VARCHAR(256), num_col INTEGER, num_col2 INTEGER)");
          } catch (SQLException ex2) {

              //ignore
              //ex2.printStackTrace();  // second time we run program
              //  should throw execption since table
              // already there
              //
              // this will have no effect on the db
          }

          try {

              // add some rows - will create duplicates if run more then =
once
              // the id column is automatically generated
              db.update1(
                  "INSERT INTO sample_table(str_col,num_col, num_col2) =
VALUES('Ford', 100, 50)");
             =20
              // do a query
              db.query1("SELECT * FROM sample_table");
             =20
             =20
             =20

             =20
              //NOW UPDATE THROUGH CONNECTION1
              db.update1("UPDATE sample_table SET num_col =3D '34' WHERE =
str_col =3D 'Ford'");

                System.out.println("From connection1: ");

              db.query1("SELECT * FROM sample_table");
             =20
              //now update throught connection2.
              db.update2("UPDATE sample_table SET num_col =3D '36' WHERE =
str_col =3D 'Ford'");
             =20
             =20
              System.out.println("From connection2: ");
              db.query2("SELECT * FROM sample_table");          =20
                                   =20
             =20
              // at end of program
              db.shutdown();
          } catch (SQLException ex3) {
              ex3.printStackTrace();
          }
      }    // main()
  }    // class Testdb




  My Test Results are:

  0 Ford 100 50=20

  From connection1:=20

  0 Ford 34 50=20

  From connection2:=20

  0 Ford 36 50=20



  Thanks a lot.=20
  Sincerely,
  Irum Godil.
  __________________________________________________
  Do You Yahoo!?
  Tired of spam? Yahoo! Mail has the best spam protection around=20
  http://mail.yahoo.com=20

[Hsqldb-developers] isolation support in HSQL 1.8.0 version

[Irum Godil <softwarengineer2004@ya...>]

Hi,
 
I downloaded HSQL version 1.8.0 from the website and wrote a small test that has 2 connection variables to the same database. I wanted to see how is locking for isolation support working.
 
Both connections update the same row of the database. Unfortunately, this is allowed in the system, and I would expect that the second connections will not be able to do so, given the locking support. Following is my test and its results. Please see the bolded red lines where I am updating the 2 rows. 
 
Also, when I run the test in version 1.7.3 I get the same results. Can someone please tell me if I have written something wrong, or if my locking understanding is incorrect. I will really appreciate your response.
 

public class IsolationIrumTest {


    Connection conn1;
    Connection conn2;
    //our connnection to the db - presist for life of program

    // we dont want this garbage collected until we are done
    public IsolationIrumTest(String db_file_name_prefix) throws Exception {    // note more general exception

        // Load the HSQL Database Engine JDBC driver
        // hsqldb.jar should be in the class path or made part of the current jar
        Class.forName("org.hsqldb.jdbcDriver");

       conn1 = DriverManager.getConnection("jdbc:hsqldb:"
                                           + db_file_name_prefix,    // filenames
                                           "sa",                     // username
                                           "");           
        
        conn2 = DriverManager.getConnection("jdbc:hsqldb:"
                + db_file_name_prefix,    // filenames
                "sa",                     // username
                "");       
        conn1.setAutoCommit(false);
        conn2.setAutoCommit(false);
    }

    public void shutdown() throws SQLException {

     update1("SHUTDOWN");
   
     
        conn1.close();    // if there are no other open connection
        conn2.close();
//        
        
       
    }
    public synchronized void query1(String expression) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn1.createStatement();         // statement objects can be reused with

        // repeated calls to execute but we
        // choose to make a new one each time
        rs = st.executeQuery(expression);    // run the query

        // do something with the result set.
        dump(rs);
        st.close();    // NOTE!! if you close a statement the associated ResultSet is

        // closed too
        // so you should copy the contents to some other object.
        // the result set is invalidated also  if you recycle an Statement
        // and try to execute some other query before the result set has been
        // completely examined.
    }

    public synchronized void query2(String expression) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn2.createStatement();         // statement objects can be reused with

        // repeated calls to execute but we
        // choose to make a new one each time
        rs = st.executeQuery(expression);    // run the query

        // do something with the result set.
        dump(rs);
        st.close();    // NOTE!! if you close a statement the associated ResultSet is

        
    }

    

    public synchronized void update1(String expression) throws SQLException {

        Statement st = null;

        st = conn1.createStatement();    // statements

        int i = st.executeUpdate(expression);    // run the query

        if (i == -1) {
            System.out.println("db error : " + expression);
        }

        st.close();
    }    // void update()

    
    
    public synchronized void update2(String expression) throws SQLException {

        Statement st = null;

        st = conn2.createStatement();    // statements

        int i = st.executeUpdate(expression);    // run the query

        if (i == -1) {
            System.out.println("db error : " + expression);
        }

        st.close();
    }    // void update()

    
    
    
    public static void dump(ResultSet rs) throws SQLException {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
        Object            o = null;

        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed

                // with 1 not 0
                System.out.print(o.toString() + " ");
            }

            System.out.println(" ");
        }
    }                                       //void dump( ResultSet rs )

    public static void main(String[] args) {

     IsolationIrumTest db = null;

        try {
            db = new IsolationIrumTest("db_file");
        } catch (Exception ex1) {
            ex1.printStackTrace();    // could not start db

            return;                   // bye bye
        }

        try {

         //FROM CONNECTION 1 
            db.update1(
                "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER, num_col2 INTEGER)");
        } catch (SQLException ex2) {

            //ignore
            //ex2.printStackTrace();  // second time we run program
            //  should throw execption since table
            // already there
            //
            // this will have no effect on the db
        }

        try {

            // add some rows - will create duplicates if run more then once
            // the id column is automatically generated
            db.update1(
                "INSERT INTO sample_table(str_col,num_col, num_col2) VALUES('Ford', 100, 50)");
            
            // do a query
            db.query1("SELECT * FROM sample_table");
            
            
            

            
            //NOW UPDATE THROUGH CONNECTION1
            db.update1("UPDATE sample_table SET num_col = '34' WHERE str_col = 'Ford'");

              System.out.println("From connection1: ");

            db.query1("SELECT * FROM sample_table");
            
            //now update throught connection2.
            db.update2("UPDATE sample_table SET num_col = '36' WHERE str_col = 'Ford'");
            
            
            System.out.println("From connection2: ");
            db.query2("SELECT * FROM sample_table");           
                                  
            
            // at end of program
            db.shutdown();
        } catch (SQLException ex3) {
            ex3.printStackTrace();
        }
    }    // main()
}    // class Testdb


 

My Test Results are:

0 Ford 100 50 

From connection1: 

0 Ford 34 50 

From connection2: 

0 Ford 36 50 

 
Thanks a lot. 
Sincerely,
Irum Godil.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [Hsqldb-developers] Thanks for the Transfer util, and patches

[fredt <fredt@us...>]

Thanks Sumit,

One-off small patches posted here are fine. For bigger changes please use 
the HSQL Utilities patch tracker on our SourceForge site.

Fred

----- Original Message ----- 
From: "Sumit Bando" <sumitbando@...>
To: <hsqldb-developers@...>
Sent: 30 March 2005 07:09
Subject: [Hsqldb-developers] Thanks for the Transfer util, and patches


Let me first congratulate the development team for an outstanding
product.

I moved our medium sized enterprise application to HSQL from Oracle in
a few hours for use in our developer environments, and I am not a
database jock.

I am equally impressed by the usefulness of the Transfer utility, which
made the porting job painless.

I did run into some issues with the Transfer util, which I will post in
a few parts. If there is a better way for me to report these, please
enlighten me.

Regards,

Sumit Bando

-----------------------------------------
Index: TransferDb.java
===================================================================
RCS file:
/cvsroot/hsqldb/hsqldb-dev/src/org/hsqldb/util/TransferDb.java,v
retrieving revision 1.11
diff -r1.11 TransferDb.java
218c218
<                         tmpTypes[i] = r.getColumnType(j);
---
>                         tmpTypes[j] = r.getColumnType(j);






__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/


-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers 

[Hsqldb-developers] Thanks for the Transfer util, and patches

[Sumit Bando <sumitbando@ya...>]

Let me first congratulate the development team for an outstanding
product.

I moved our medium sized enterprise application to HSQL from Oracle in
a few hours for use in our developer environments, and I am not a
database jock.

I am equally impressed by the usefulness of the Transfer utility, which
made the porting job painless.

I did run into some issues with the Transfer util, which I will post in
a few parts. If there is a better way for me to report these, please
enlighten me.

Regards,

Sumit Bando

-----------------------------------------
Index: TransferDb.java
===================================================================
RCS file:
/cvsroot/hsqldb/hsqldb-dev/src/org/hsqldb/util/TransferDb.java,v
retrieving revision 1.11
diff -r1.11 TransferDb.java
218c218
<                         tmpTypes[i] = r.getColumnType(j);
---
>                         tmpTypes[j] = r.getColumnType(j);





		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

Re: [Hsqldb-developers] Transactions in Hsql

[Irum Godil <softwarengineer2004@ya...>]

Just to confirm the 1.8.0 version in CVS is the one under the directory: "hsqldb-dev" right. This is the latest one right?
 
thanks a lot for your help.

fredt <fredt@...> wrote:
Yes, the book mentions an important principle which some inexperienced programmers do not upderstand.
 
A DBMS cannot ensure consistency, as the meaning of this is always defined as a business rule and is application-dependent.
 
The LockFile class prevents two separate java processess to open the database at the same time. It has no role in managing transaction capabilities. Once a process has opened the database, the lock file prevents any other process to open it.
 
Regarding Isolation, older versions allowed two uncommitted transactions both to modify the same rows. The current version in CVS disallows this. It maintains a write lock.
 
Regarding persistence, durability is achieved by writing all insert and update operations to the .log file as they occur. The Table class activates the write and it is performed by the ScriptWriterText class.
 
BTW. If you have not actually done much coding work for your project, I would recommend to focus your attention on the CVS version of 1.8.0 (or at least RC9) which is up to date. This will make your work more useful. The program structure has not changed since 1.7.3, but improtant aspects such as TX support are changing.
 
 
Fred
 
----- Original Message ----- 
From: Irum Godil 
To: hsqldb-developers@... 
Sent: 27 March 2005 05:20
Subject: Re: [Hsqldb-developers] Transactions in Hsql


Hi Fred, 
 
I was reading a book on Transaction Management namely: "Databases and Transaction Processing" by Lewis, Bernstein and Kifer; and I have gotten a little confused as to the Transaction Support in HSQL. Following are my concerns and I will really appreciate if you can answer them: 
 
1) On Consistency: In chapter 20 the book mentions that: "Keep in mind that producing consistent transactions is the sole responsibility of teh application programmer. The remainder of the transaction processing system takes consistecny as a given and provides atomicity, isolation and durability..." 
 
Also, at another point the book mentions that: "It is the transaction designer's job to design consistent transactions. It is assumed that the Transaction Processing monitor provides the abstraction of atomicity, isolation, and durability". 
 
From this my concern is that is there no support in the HSQL code base to support "Consistency" in transactions? Is it only the job of the person writing the transactions to ensure that transactions are consistent? If there is support in HSQL code base purely for the "Consistency" feature in ACID, then where and what is it? This is extremely important for my project since I want to refactor those places out. 
 
2) Isolation: You have mentioned below in your email that HSQL does not support Isolation levels. But then what is the purpose of the LockFile class that is present? 
 
Also, since HSQL supports READ-UNCOMMITED Isolation, there should be some way of obtatining and releasing locks for the write operations right, so where and how are these implemented?
 
Also, is the entire table locked for writing operations; or what is exactly locked?
 
3) Persistence: 
You have mentioned below that Durability is achieved by persisting data to one or two files. Which files are these and again which classes in HSQL support this. I understand it to be the Logger and the Log classes; but those maintain the log right. Is the database stored separately in some other file?
 
I will really appreciate your responses to the above and they will tremendously help me in my research. 
 
Thanks.
Sincerely,
Irum Godil .
 
 

fredt <fredt@...> wrote:
Your understanding of the implementation is correct. There is no transaction isolation in the version you have studied, but this feature is being added. The methods for isolation levels are part of the JDBC standard.
 
Isolation may be necessary for certain applications, especially existing and legacy applications. But serious applications such as banking can be designed with highly normalised schemas without any transaction isolation. 
 
Durability is achieved by persisting data to one or two files. HSQLDB use file sync so that changes to data are fully persisted at user-defined intervals. This translates to about 99.999% reliability for new data, given an average one crash a day. As actual systems are probably 10 - 100 times more reliable, then it translates to up to 99.99999% reliability. Note that even in the event of a crash, the record of data changes that occured prior to the last interval would remain intact.
 
Fred Toussi
----- Original Message ----- 
From: Irum Godil 
To: hsqldb-developers@... 
Sent: 28 February 2005 16:26
Subject: [Hsqldb-developers] Transactions in Hsql


Hi,
 
I am a graduate student at the University of Toronto. I am working in Aspect Oriented Refactorings, and am interested in exploring the HSql codebase for Transactions refactoring. 
 
I would like to understand how are transactions implemented in HSql. From my inspection of Hsql code base, I have come to the following conclusions. I was hoping if someone can confirm that these are correct, and also point out anything I may have left out. 
 
1) Atomicity: Atomicity is obtained via commit and Rollback features. By default AutoCommit is turned on, but if we want to treat a collection of commands as a transaction, then we could set autoCommit(false) and have explicit commit after the statements
 
2) Consistency: This is obtained via Locking the Database file while it is being used, and supported via LockFile.java class and its uses
 
3) Isolation Level: I see that HSql supports Transaction_Read_Uncommited, however in the code I only saw calls to setting and getting the isolation level. What other classes/methods support Isolation in HSql?
 
4) Durabiltiy: I am assuming that the whole org.hsqldb.persist package is supporting this via persisting data. Am I right?
 
Are there any documentations available on Transactions in HSql? I will really appreciate your help on this. 
 
Thanks a lot. 
Sincerely,
Irum Godil.


---------------------------------
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.

---------------------------------
Do you Yahoo!?
Yahoo! Small Business - Try our new resources site! 
__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [Hsqldb-developers] Closing of Databases

[fredt <fredt@us...>]

You should execute the SQL command "SHUTDOWN" to shutdown the database. =
You are obviously not doing this and relying on autorecovery at the next =
startup. Please read the documentation.

Fred
  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsql=20
  Sent: 29 March 2005 19:43
  Subject: [Hsqldb-developers] Closing of Databases


  Hi,=20
  I am noticing that in the version, I am using of HSQL, the .log file =
is never deleted upon shutdown, and it is appended to the script file =
only on the next restart of the application.=20

  I traced through the code and found out that this is happening because =
there is no call to DatabaseManger.closeDatabases( closeMode) function, =
which is supposed to close the database. I added this line into =
Session.close( ) function and it works fine now. The database log file =
is appended to the script file upon shutdown. I wanted to know if this =
is a known bug? Maybe it is already taken care of in the next version.=20

  thanks.=20


-------------------------------------------------------------------------=
-----
  Do you Yahoo!?
  Yahoo! Mail - Helps protect you from nasty viruses.

[Hsqldb-developers] Closing of Databases

[Irum Godil <softwarengineer2004@ya...>]

Hi, 
I am noticing that in the version, I am using of HSQL, the .log file is never deleted upon shutdown, and it is appended to the script file only on the next restart of the application. 
 
I traced through the code and found out that this is happening because there is no call to DatabaseManger.closeDatabases( closeMode) function, which is supposed to close the database. I added this line into Session.close( ) function and it works fine now. The database log file is appended to the script file upon shutdown. I wanted to know if this is a known bug? Maybe it is already taken care of in the next version. 
 
thanks. 

		
---------------------------------
Do you Yahoo!?
 Yahoo! Mail - Helps protect you from nasty viruses.

Re: [Hsqldb-developers] Transactions in Hsql

[fredt <fredt@us...>]

Yes, the book mentions an important principle which some inexperienced =
programmers do not upderstand.

A DBMS cannot ensure consistency, as the meaning of this is always =
defined as a business rule and is application-dependent.

The LockFile class prevents two separate java processess to open the =
database at the same time. It has no role in managing transaction =
capabilities. Once a process has opened the database, the lock file =
prevents any other process to open it.

Regarding Isolation, older versions allowed two uncommitted transactions =
both to modify the same rows. The current version in CVS disallows this. =
It maintains a write lock.

Regarding persistence, durability is achieved by writing all insert and =
update operations to the .log file as they occur. The Table class =
activates the write and it is performed by the ScriptWriterText class.

BTW. If you have not actually done much coding work for your project, I =
would recommend to focus your attention on the CVS version of 1.8.0 (or =
at least RC9) which is up to date. This will make your work more useful. =
The program structure has not changed since 1.7.3, but improtant aspects =
such as TX support are changing.


Fred

  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsqldb-developers@...=20
  Sent: 27 March 2005 05:20
  Subject: Re: [Hsqldb-developers] Transactions in Hsql


  Hi Fred,=20

  I was reading a book on Transaction Management namely: "Databases and =
Transaction Processing" by Lewis, Bernstein and Kifer; and I have gotten =
a little confused as to the Transaction Support in HSQL. Following are =
my concerns and I will really appreciate if you can answer them:=20

  1) On Consistency: In chapter 20 the book mentions that: "Keep in mind =
that producing consistent transactions is the sole responsibility of teh =
application programmer. The remainder of the transaction processing =
system takes consistecny as a given and provides atomicity, isolation =
and durability..."=20

  Also, at another point the book mentions that: "It is the transaction =
designer's job to design consistent transactions. It is assumed that the =
Transaction Processing monitor provides the abstraction of atomicity, =
isolation, and durability".=20

  From this my concern is that is there no support in the HSQL code base =
to support "Consistency" in transactions? Is it only the job of the =
person writing the transactions to ensure that transactions are =
consistent? If there is support in HSQL code base purely for the =
"Consistency" feature in ACID, then where and what is it? This is =
extremely important for my project since I want to refactor those places =
out.=20

  2) Isolation: You have mentioned below in your email that HSQL does =
not support Isolation levels. But then what is the purpose of the =
LockFile class that is present?=20

  Also, since HSQL supports READ-UNCOMMITED Isolation, there should be =
some way of obtatining and releasing locks for the write operations =
right, so where and how are these implemented?

  Also, is the entire table locked for writing operations; or what is =
exactly locked?

  3) Persistence:=20
  You have mentioned below that Durability is achieved by persisting =
data to one or two files. Which files are these and again which classes =
in HSQL support this. I understand it to be the Logger and the Log =
classes; but those maintain the log right. Is the database stored =
separately in some other file?

  I will really appreciate your responses to the above and they will =
tremendously help me in my research.=20

  Thanks.
  Sincerely,
  Irum Godil .



  fredt <fredt@...> wrote:
    Your understanding of the implementation is correct. There is no =
transaction isolation in the version you have studied, but this feature =
is being added. The methods for isolation levels are part of the JDBC =
standard.

    Isolation may be necessary for certain applications, especially =
existing and legacy applications. But serious applications such as =
banking can be designed with highly normalised schemas without any =
transaction isolation.=20

    Durability is achieved by persisting data to one or two files. =
HSQLDB use file sync so that changes to data are fully persisted at =
user-defined intervals. This translates to about 99.999% reliability for =
new data, given an average one crash a day. As actual systems are =
probably 10 - 100 times more reliable, then it translates to up to =
99.99999% reliability. Note that even in the event of a crash, the =
record of data changes that occured prior to the last interval would =
remain intact.

    Fred Toussi
      ----- Original Message -----=20
      From: Irum Godil=20
      To: hsqldb-developers@...=20
      Sent: 28 February 2005 16:26
      Subject: [Hsqldb-developers] Transactions in Hsql


      Hi,

      I am a graduate student at the University of Toronto. I am working =
in Aspect Oriented Refactorings, and am interested in exploring the HSql =
codebase for Transactions refactoring.=20

      I would like to understand how are transactions implemented in =
HSql. From my inspection of Hsql code base, I have come to the following =
conclusions. I was hoping if someone can confirm that these are correct, =
and also point out anything I may have left out.=20

      1) Atomicity: Atomicity is obtained via commit and Rollback =
features. By default AutoCommit is turned on, but if we want to treat a =
collection of commands as a transaction, then we could set =
autoCommit(false) and have explicit commit after the statements

      2) Consistency: This is obtained via Locking the Database file =
while it is being used, and supported via LockFile.java class and its =
uses

      3) Isolation Level: I see that HSql supports =
Transaction_Read_Uncommited, however in the code I only saw calls to =
setting and getting the isolation level. What other classes/methods =
support Isolation in HSql?

      4) Durabiltiy: I am assuming that the whole org.hsqldb.persist =
package is supporting this via persisting data. Am I right?

      Are there any documentations available on Transactions in HSql? I =
will really appreciate your help on this.=20

      Thanks a lot.=20
      Sincerely,
      Irum Godil.


-------------------------------------------------------------------------=
-
      Do you Yahoo!?
      Yahoo! Mail - You care about security. So do we.


-------------------------------------------------------------------------=
-----
  Do you Yahoo!?
  Yahoo! Small Business - Try our new resources site! 

Re: [Hsqldb-developers] Persist on a different disk

[Blaine Simpson <blaine.simpson@ad...>]

Specify whatever file path you want in in your jdbc:hsqldb:file: JDBC
URL or for the server.database.X setting in your server/webserver
properties file.

See Chapters 1 and 4 of the Guide.


Irum Godil wrote:

> Is it possible in HSQL to persist the data on a different disk or media?
>  
> thanks.
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>


-- 
ICF:  703-934-3692       Cell:  703-944-9317

[Hsqldb-developers] Persist on a different disk

[Irum Godil <softwarengineer2004@ya...>]

Is it possible in HSQL to persist the data on a different disk or media?
 
thanks.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [Hsqldb-developers] Transactions in Hsql

[Irum Godil <softwarengineer2004@ya...>]

Hi Fred, 
 
I was reading a book on Transaction Management namely: "Databases and Transaction Processing" by Lewis, Bernstein and Kifer; and I have gotten a little confused as to the Transaction Support in HSQL. Following are my concerns and I will really appreciate if you can answer them: 
 
1) On Consistency: In chapter 20 the book mentions that: "Keep in mind that producing consistent transactions is the sole responsibility of teh application programmer. The remainder of the transaction processing system takes consistecny as a given and provides atomicity, isolation and durability..." 
 
Also, at another point the book mentions that: "It is the transaction designer's job to design consistent transactions. It is assumed that the Transaction Processing monitor provides the abstraction of atomicity, isolation, and durability". 
 
From this my concern is that is there no support in the HSQL code base to support "Consistency" in transactions? Is it only the job of the person writing the transactions to ensure that transactions are consistent? If there is support in HSQL code base purely for the "Consistency" feature in ACID, then where and what is it? This is extremely important for my project since I want to refactor those places out. 
 
2) Isolation: You have mentioned below in your email that HSQL does not support Isolation levels. But then what is the purpose of the LockFile class that is present? 
 
Also, since HSQL supports READ-UNCOMMITED Isolation, there should be some way of obtatining and releasing locks for the write operations right, so where and how are these implemented?
 
Also, is the entire table locked for writing operations; or what is exactly locked?
 
3) Persistence: 
You have mentioned below that Durability is achieved by persisting data to one or two files. Which files are these and again which classes in HSQL support this. I understand it to be the Logger and the Log classes; but those maintain the log right. Is the database stored separately in some other file?
 
I will really appreciate your responses to the above and they will tremendously help me in my research. 
 
Thanks.
Sincerely,
Irum Godil .
 
 

fredt <fredt@...> wrote:
Your understanding of the implementation is correct. There is no transaction isolation in the version you have studied, but this feature is being added. The methods for isolation levels are part of the JDBC standard.
 
Isolation may be necessary for certain applications, especially existing and legacy applications. But serious applications such as banking can be designed with highly normalised schemas without any transaction isolation. 
 
Durability is achieved by persisting data to one or two files. HSQLDB use file sync so that changes to data are fully persisted at user-defined intervals. This translates to about 99.999% reliability for new data, given an average one crash a day. As actual systems are probably 10 - 100 times more reliable, then it translates to up to 99.99999% reliability. Note that even in the event of a crash, the record of data changes that occured prior to the last interval would remain intact.
 
Fred Toussi
----- Original Message ----- 
From: Irum Godil 
To: hsqldb-developers@... 
Sent: 28 February 2005 16:26
Subject: [Hsqldb-developers] Transactions in Hsql


Hi,
 
I am a graduate student at the University of Toronto. I am working in Aspect Oriented Refactorings, and am interested in exploring the HSql codebase for Transactions refactoring. 
 
I would like to understand how are transactions implemented in HSql. From my inspection of Hsql code base, I have come to the following conclusions. I was hoping if someone can confirm that these are correct, and also point out anything I may have left out. 
 
1) Atomicity: Atomicity is obtained via commit and Rollback features. By default AutoCommit is turned on, but if we want to treat a collection of commands as a transaction, then we could set autoCommit(false) and have explicit commit after the statements
 
2) Consistency: This is obtained via Locking the Database file while it is being used, and supported via LockFile.java class and its uses
 
3) Isolation Level: I see that HSql supports Transaction_Read_Uncommited, however in the code I only saw calls to setting and getting the isolation level. What other classes/methods support Isolation in HSql?
 
4) Durabiltiy: I am assuming that the whole org.hsqldb.persist package is supporting this via persisting data. Am I right?
 
Are there any documentations available on Transactions in HSql? I will really appreciate your help on this. 
 
Thanks a lot. 
Sincerely,
Irum Godil.


---------------------------------
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
		
---------------------------------
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: [Hsqldb-developers] Scripting in HSQL

[Campbell Boucher-Burnet <boucherb@us...>]

As an idea for the future, when there is time and bodies available...

With minimal work, it might be easy to add a complementary LOAD command.

I think the major obstacle is that SCRIPT currently always includes the
database users, which I've found to be the overriding issue when
attempting to use the SCRIPT <file> output to populate another instance
(have to manually edit file to remove users, since SA and other common
user names are likely to clash with target db).

Perhaps the easiest easy solution to this would be

SCRIPT <file> [WITH OPTION] [NO] USERS

Obvious extensions are:

[NO] DATA
[NO] DDL -- synonym for USERS, TABLES, CONSTRAINTS, INDEXES, GRANTS...
[NO] TABLES 
[NO] CONSTRAINTS
[NO] INDEXES
[NO] GRANTS
[NO] ALIASES
etc.

e.g.

SCRIPT <file> DATA -- just scripts the data
SCRIPT <file> NO DATA -- scripts all DDL  but no data
SCRIPT <file> NO USERS -- scripts DML and all DDL except user
definitions


LOAD <file> would then only need to do a simple job:  execute a script
file in the form created by SCRIPT, rather than having to include logic
to ignore certain DDL and DML forms (which would require a major
reworking of DatabaseCommandInterpreter)

A less obvious (but quite interesting) extension would be:

[USING] FILE PER OBJECT (e.g. MY_TABLE.tab, MY_TABLE.dat,
MY_TABLE_INDEX_1.idx)

of course, this last point is treading on a full internal dump/restore
facility, which would likely better be implemented at a lower level (for
efficiency and safety).

C:\


On Sat, 2005-03-19 at 18:23, fredt wrote:
> The SCRIPT <filename> command uses ScriptWriterText. The readers are
> not currently used with all-in-memory databases, but they might in the
> future for importing data. We also have a files_readonly mode that
> uses the readers.
>  
> Fred
>         ----- Original Message ----- 
>         From: Irum Godil
>         To: hsqldb-developers@...
>         Sent: 19 March 2005 23:24
>         Subject: Re: [Hsqldb-developers] Scripting in HSQL
>         
>         But all the files in scriptio package such as
>         ScriptWriterText, ScriptReaderBase, etc. are only useful for
>         persistent databases right?
>         
>         fredt <fredt@...> wrote: 
>                 Sure, see the documentation. Both "SCRIPT" and "SCRIPT
>                 <filename>" can be used for pure in-memory databases
>                 and are obviously useful commands.
>                  
>                 Fred
>                         ----- Original Message ----- 
>                         From: Irum Godil
>                         To: hsql
>                         Sent: 19 March 2005 22:01
>                         Subject: [Hsqldb-developers] Scripting in HSQL
>                         
>                         Hi, 
>                          
>                         I would like to know that is the SCRIPT
>                         Statement on the database only applied in case
>                         of Persistent databases, or is it also useful
>                         for pure-in memory databases? I am assuming
>                         that all the ScriptWriter and Reader classes
>                         are used in the context of Persistent
>                         databases only, but I am not too sure about
>                         the org.hsqldb.DatabaseScript class, if it can
>                         be used for transient databases as well? 
>                          
>                         Any help will be appreciated. I am refactoring
>                         the application to only support In-Memory
>                         databases in the code, and make persistence an
>                         aspect, that is why I need to be sure what is
>                         useful for only persistent cases. 
>                          
>                         Thanks a lot. 
>                         Sincerely, 
>                         Irum Godil. 
>                         
>                         
>                         ______________________________________________
>                          Do you Yahoo!?
>                         Yahoo! Mail - Find what you need with new
>                         enhanced search. Learn more.
>         
>         
>         ______________________________________________________________
>          Do you Yahoo!?
>         Yahoo! Small Business - Try our new resources site!

Re: [Hsqldb-developers] Scripting in HSQL

[fredt <fredt@us...>]

The SCRIPT <filename> command uses ScriptWriterText. The readers are not =
currently used with all-in-memory databases, but they might in the =
future for importing data. We also have a files_readonly mode that uses =
the readers.

Fred
  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsqldb-developers@...=20
  Sent: 19 March 2005 23:24
  Subject: Re: [Hsqldb-developers] Scripting in HSQL


  But all the files in scriptio package such as ScriptWriterText, =
ScriptReaderBase, etc. are only useful for persistent databases right?

  fredt <fredt@...> wrote:=20
    Sure, see the documentation. Both "SCRIPT" and "SCRIPT <filename>" =
can be used for pure in-memory databases and are obviously useful =
commands.

    Fred
      ----- Original Message -----=20
      From: Irum Godil=20
      To: hsql=20
      Sent: 19 March 2005 22:01
      Subject: [Hsqldb-developers] Scripting in HSQL


      Hi,=20

      I would like to know that is the SCRIPT Statement on the database =
only applied in case of Persistent databases, or is it also useful for =
pure-in memory databases? I am assuming that all the ScriptWriter and =
Reader classes are used in the context of Persistent databases only, but =
I am not too sure about the org.hsqldb.DatabaseScript class, if it can =
be used for transient databases as well?=20

      Any help will be appreciated. I am refactoring the application to =
only support In-Memory databases in the code, and make persistence an =
aspect, that is why I need to be sure what is useful for only persistent =
cases.=20

      Thanks a lot.=20
      Sincerely,=20
      Irum Godil.=20


-------------------------------------------------------------------------=
-
      Do you Yahoo!?
      Yahoo! Mail - Find what you need with new enhanced search. Learn =
more.


-------------------------------------------------------------------------=
-----
  Do you Yahoo!?
  Yahoo! Small Business - Try our new resources site! 

Re: [Hsqldb-developers] Scripting in HSQL

[Irum Godil <softwarengineer2004@ya...>]

But all the files in scriptio package such as ScriptWriterText, ScriptReaderBase, etc. are only useful for persistent databases right?

fredt <fredt@...> wrote:Sure, see the documentation. Both "SCRIPT" and "SCRIPT <filename>" can be used for pure in-memory databases and are obviously useful commands.
 
Fred
----- Original Message ----- 
From: Irum Godil 
To: hsql 
Sent: 19 March 2005 22:01
Subject: [Hsqldb-developers] Scripting in HSQL


Hi, 
 
I would like to know that is the SCRIPT Statement on the database only applied in case of Persistent databases, or is it also useful for pure-in memory databases? I am assuming that all the ScriptWriter and Reader classes are used in the context of Persistent databases only, but I am not too sure about the org.hsqldb.DatabaseScript class, if it can be used for transient databases as well? 
 
Any help will be appreciated. I am refactoring the application to only support In-Memory databases in the code, and make persistence an aspect, that is why I need to be sure what is useful for only persistent cases. 
 
Thanks a lot. 
Sincerely, 
Irum Godil. 


---------------------------------
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. Learn more.

		
---------------------------------
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site! 

Re: [Hsqldb-developers] Scripting in HSQL

[fredt <fredt@us...>]

Sure, see the documentation. Both "SCRIPT" and "SCRIPT <filename>" can =
be used for pure in-memory databases and are obviously useful commands.

Fred
  ----- Original Message -----=20
  From: Irum Godil=20
  To: hsql=20
  Sent: 19 March 2005 22:01
  Subject: [Hsqldb-developers] Scripting in HSQL


  Hi,=20

  I would like to know that is the SCRIPT Statement on the database only =
applied in case of Persistent databases, or is it also useful for =
pure-in memory databases? I am assuming that all the ScriptWriter and =
Reader classes are used in the context of Persistent databases only, but =
I am not too sure about the org.hsqldb.DatabaseScript class, if it can =
be used for transient databases as well?=20

  Any help will be appreciated. I am refactoring the application to only =
support In-Memory databases in the code, and make persistence an aspect, =
that is why I need to be sure what is useful for only persistent cases.=20

  Thanks a lot.=20
  Sincerely,=20
  Irum Godil.=20


-------------------------------------------------------------------------=
-----
  Do you Yahoo!?
  Yahoo! Mail - Find what you need with new enhanced search. Learn more.

[Hsqldb-developers] Scripting in HSQL

[Irum Godil <softwarengineer2004@ya...>]

Hi, 
 
I would like to know that is the SCRIPT Statement on the database only applied in case of Persistent databases, or is it also useful for pure-in memory databases? I am assuming that all the ScriptWriter and Reader classes are used in the context of Persistent databases only, but I am not too sure about the org.hsqldb.DatabaseScript class, if it can be used for transient databases as well? 
 
Any help will be appreciated. I am refactoring the application to only support In-Memory databases in the code, and make persistence an aspect, that is why I need to be sure what is useful for only persistent cases. 
 
Thanks a lot. 
Sincerely, 
Irum Godil. 

		
---------------------------------
Do you Yahoo!?
 Yahoo! Mail - Find what you need with new enhanced search. Learn more.

[Hsqldb-developers] Need Test Files for Collations

[fredt <fredt@us...>]

For OpenOffice.org integration, we have developed a new feature to =
specify the collation (sorting order) for all character-based columns in =
the database.

The collations we are planning to use are listed below. We need word =
list files for different languages that demonstrate how different words =
should be listed. If you are a speaker of a language with special =
collation rules, please submit a word list.

Fred Toussi
Maintainer, HSQLDB Project


The word lists should be in CSV format, encoded as UTF-8. A test file =
should contain words that demonstrate the collation rules of one =
particular language, including use of accents. Words should be sorted =
and numbered correctly as in the following example:

1, "alpha"
2, "beta"
3, "gamma"
...

List of collations are as follows:

      Collation name Locale=20
      Afrikaans af-ZA=20
      Amharic am-ET=20
      Arabic ar=20
      Assamese as-IN=20
      Azerbaijani_Latin az-AZ=20
      Azerbaijani_Cyrillic az-cyrillic=20
      Belarusian be-BY=20
      Bulgarian bg-BG=20
      Bengali bn-IN=20
      Tibetan bo-CN=20
      Bosnian bs-BA=20
      Catalan ca-ES=20
      Czech cs-CZ=20
      Welsh cy-GB=20
      Danish da-DK=20
      German de-DE=20
      Greek el-GR=20
      Latin1_General en-US=20
      Spanish es-ES=20
      Estonian et-EE=20
      Basque eu=20
      Finnish fi-FI=20
      French fr-FR=20
      Guarani gn-PY=20
      Gujarati gu-IN=20
      Hausa ha-NG=20
      Hebrew he-IL=20
      Hindi hi-IN=20
      Croatian hr-HR=20
      Hungarian hu-HU=20
      Armenian hy-AM=20
      Indonesian id-ID=20
      Igbo ig-NG=20
      Icelandic is-IS=20
      Italian it-IT=20
      Inuktitut iu-CA=20
      Japanese ja-JP=20
      Georgian ka-GE=20
      Kazakh kk-KZ=20
      Khmer km-KH=20
      Kannada kn-IN=20
      Korean ko-KR=20
      Konkani kok-IN=20
      Kashmiri ks=20
      Kirghiz ky-KG=20
      Lao lo-LA=20
      Lithuanian lt-LT=20
      Latvian lv-LV=20
      Maori mi-NZ=20
      Macedonian mk-MK=20
      Malayalam ml-IN=20
      Mongolian mn-MN=20
      Manipuri mni-IN=20
      Marathi mr-IN=20
      Malay ms-MY=20
      Maltese mt-MT=20
      Burmese my-MM=20
      Danish_Norwegian nb-NO=20
      Nepali ne-NP=20
      Dutch nl-NL=20
      Norwegian nn-NO=20
      Oriya or-IN=20
      Punjabi pa-IN=20
      Polish pl-PL=20
      Pashto ps-AF=20
      Portuguese pt-PT=20
      Romanian ro-RO=20
      Russian ru-RU=20
      Sanskrit sa-IN=20
      Sindhi sd-IN=20
      Slovak sk-SK=20
      Slovenian sl-SI=20
      Somali so-SO=20
      Albanian sq-AL=20
      Serbian_Cyrillic sr-YU=20
      Swedish sv-SE=20
      Swahili sw-KE=20
      Tamil ta-IN=20
      Telugu te-IN=20
      Tajik tg-TJ=20
      Thai th-TH=20
      Turkmen tk-TM=20
      Tswana tn-BW=20
      Turkish tr-TR=20
      Tatar tt-RU=20
      Ukrainian uk-UA=20
      Urdu ur-PK=20
      Uzbek_Latin uz-UZ=20
      Venda ven-ZA=20
      Vietnamese vi-VN=20
      Yoruba yo-NG=20
      Chinese zh-CN=20
      Zulu zu-ZA=20

[Hsqldb-developers] org.hsqldb package added to RefactorIT open source community licence

[Campbell Boucher-Burnet <boucherb@us...>]

Hi all.

I sent in a request a while back, and it looks like we've been added.

http://www.refactorit.com/?id=5339

It's a nice incentive to use the tool for anyone looking to help out
with better modularizing/virtualizing the various hsqldb sub-components.

[Hsqldb-developers] Identifying PRIMARY KEY, FOREIGN KEY and UNIQUE columns.

[Kostis Kyzirakos <kkyzir@db...>]

Hello there!
I use the C# version of SharpHSQL (which is greeeeeeeeeeeeeeeeeeat by 
the way) but I don't think that it matters.
I create a table by executing the database.execute("CREATE TABLE...").
I get the SharpHSQL.Table back using the d.getTable("table_name", 
channel) command.
How can I find out which is the PRIMARY KEY column(s), the FOREIGN KEY 
column(s) and the UNIQUE columns?
I tried analyzing the CONSTRAINTS that I can get by using the 
Table.getConstraints() functions but I couldn't solve my problem.

If there is another way that I can find out about the PRIMARY KEY, 
FOREIGN KEY and UNIQUE constraints please tell me so!

Thanks everybody!

Re: [Hsqldb-developers] Transaction Commit

[Campbell Boucher-Burnet <boucherb@us...>]

:(  Guess I'll have to move to the city.

Anyway, yes: it is kind of hard.

Think about it:

You have a huge table, you add a column, which updates all rows in
column to have a value.=20

If you have no default and the column is NULL(able), then the update =
is
to all NULL values.

If you have a default value, then the update is to the specified
DEFAULT.

(the remaining case is illegal:  add column not null with no default)

If you do not implement sophisticated, versioned, row-wise incrementa=
lly
update-able structural meta data, then the update has occurred fully =
and
literally (i.e. on disk and/or in memory) =20

Say you have not implemented the features in the previous paragraph.
Then you decide to roll back.  Arg.  Huge amount of wasted work.

Say you have implemented the features for versioned, row-wise
incrementally update-able structural meta data (i.e. the system does =
not
need to make any actual physical row structure changes until it
encounters rows whose meta data version stamps are older than the glo=
bal
stamp for the owning table's current meta data structure).

Example:

Table(current meta data version index)
has list of (meta data descriptor), list of (row (data, meta data
version number))

Then you can perform transactions that intermingle changes to a table=
's
structure with updates to its row data, and only rows touched since a
change to table structure need to really be physically modified, in a
structural, rather than content sense)

But now you must implement not only row data versions, but also row
structure meta data versions in your rollback structures.  Arg.

Now add indexes (same thing as rows...need to record structural meta
data version), check constraints (same)  and foreign keys (same) into
the picture.

Now I'm sure there's lots of things I've missed...

=2E.. and the last they saw of Campbell, he was screaming, pulling hi=
s
hair out and disappearing over the horizon at high speed.

Get the picture?=20

One day, in the dim, distant future, this issue may be addressed, but
certainly not before other far more pressing issues have been address=
ed,
such a:

a) better than read committed isolation
b) decent query optimization under complex joins and predicates
c) efficient, rather than since-the-beginning-of-time checkpoints and
recovery for cached/text tables

Although the ability to perform structural modifications in a
transactional manner is certainly convenient and highly desirable,
I've always felt that if one's database (or application/business) obj=
ect
definitions are changing so rapidly that one dreams of DBMS/IDE that
support all mutations with ease and in a transactional setting, then =
its
pretty likely that either:

1.) there was not enough time/money/manpower up front on the project
2.) Development is proceeding too literally and is reacting
incrementally at too fine a granularity to changing requirements.=
=20
(i.e. without the required effort/expertize applied to normalizing fo=
r
flexibility and refining appropriate domain generalizations)=20

Given the code I have to deal with in my daily legacy application
support role, I'd say a mediocre tool in the hands of a great team
will almost always produce better results than a great tool in the ha=
nds
of a crappy team or used in a hostile or anti-productive development
environment (under dark-side management, in a sweat-shop or political=
ly
challenged organization, etc.)


On Sun, 2005-03-13 at 04:25, Rainer Pr=C3=B6bster wrote:
> Hello Lorna,
>=20
> you have lost your farm: Postgresql does support this! :-)
> I.e. with Postgresql you can manipulate ddl within every transactio=
n and roll back the whole transaction if an error occurs.
> This is a truely professional feature. Even more: If you have to ma=
nipulate your ddl often (in productive systems), this feature is fund=
amental!
> I did know that Oracle (8.x) did not support this and I was really =
surprprised that time ... what a shame for such a big and expensive s=
ystem!
> As I don't know how hard it is to implement this, I won't suggest .=
.. ;-)
>=20
> Cheers,
> Rainer
>=20
>=20
>=20
> Lorna Burnet wrote:
>=20
> >It happens because in hsqlsb, currently, data definition language =
execution is not "transactional"
> >
> >That is, you cannot roll back table creation, etc.
> >
> >So, if you issue ddl in the middle of a transaction, this is equiv=
alent to an implicit commit.
> >
> >The same is true of many DBMS, including Oracle, MS SQL Server, et=
c.  I can't say off the top of my head, but I'd bet the farm that the=
 same applies to Postgresql, MySQL, etc.  In fact, I think you'd have=
 to really search carefully to find the small handfull of systems tha=
t actually support ddl operations as undoable transaction items.
> >
> >
> >Where HSQLDB really needs work regarding ddl is not to support und=
oable itms, but rather to support cross-session ddl locks.  For insta=
nce, try updating a table inside a transaction in one session, then d=
rop the table inside another session, and then roll back the first se=
ssion.  Likely, you'll get a "table not found" exception in the first=
 transaction, whereas IMO it would probably be better to raise an exc=
eption in the second session, stating that one cannot drop or alter t=
he table, because it is in use by another session.
> >----- Original Message -----
> >From: Irum Godil <softwarengineer2004@...>
> >Date: Friday, March 11, 2005 11:06 pm
> >Subject: [Hsqldb-developers] Transaction Commit
> >
> > =20
> >
> >>Hi,=20
> >>I am going through the Hsql Database code, and I noticed that in=
=20
> >>the class DatabaseCommandInterpreter the call to Session.commit i=
s=20
> >>happening in a few places. One such example is in method:=20
> >>
> >>private void processCreateTable(int type) throws HsqlException {.=
..}
> >>
> >>I see that if I have created a table, done some transactions on t=
he=20
> >>table, and now create a new table; then all the transactions made=
=20
> >>on the first table are committed by call to function:=20
> >>
> >>Session.Commit( )
> >>
> >>I do not understand why is this being done. If I have turned=20
> >>AutoCommit to false, then I do not want any commits to happen unt=
il=20
> >>I explicitly call commit( ) right? So, if some transactions are=
=20
> >>being committed, then is not that wrong. How is atomicity of=20
> >>transactions preserved in such a case?
> >>
> >>Thanks.=20
> >>
> >>
> >>
> >>
> >>       =09
> >>---------------------------------
> >>Do you Yahoo!?
> >>Yahoo! Small Business - Try our new resources site!=20
> >>   =20
> >>
> >
> >
> >
> >-------------------------------------------------------
> >SF email is sponsored by - The IT Product Guide
> >Read honest & candid reviews on hundreds of IT Products from real =
users.
> >Discover which products truly live up to the hype. Start reading n=
ow.
> >http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
> >_______________________________________________
> >hsqldb-developers mailing list
> >hsqldb-developers@...
> >https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
> >
> > =20
> >
>=20
>=20
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real u=
sers.
> Discover which products truly live up to the hype. Start reading no=
w.
> http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
> _______________________________________________
> hsqldb-developers mailing list
> hsqldb-developers@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

Re: [Hsqldb-developers] Transaction Commit

[<rpr@vr...>]

Hello Lorna,

you have lost your farm: Postgresql does support this! :-)
I.e. with Postgresql you can manipulate ddl within every transaction and roll back the whole transaction if an error occurs.
This is a truely professional feature. Even more: If you have to manipulate your ddl often (in productive systems), this feature is fundamental!
I did know that Oracle (8.x) did not support this and I was really surprprised that time ... what a shame for such a big and expensive system!
As I don't know how hard it is to implement this, I won't suggest ... ;-)

Cheers,
Rainer



Lorna Burnet wrote:

>It happens because in hsqlsb, currently, data definition language execution is not "transactional"
>
>That is, you cannot roll back table creation, etc.
>
>So, if you issue ddl in the middle of a transaction, this is equivalent to an implicit commit.
>
>The same is true of many DBMS, including Oracle, MS SQL Server, etc.  I can't say off the top of my head, but I'd bet the farm that the same applies to Postgresql, MySQL, etc.  In fact, I think you'd have to really search carefully to find the small handfull of systems that actually support ddl operations as undoable transaction items.
>
>
>Where HSQLDB really needs work regarding ddl is not to support undoable itms, but rather to support cross-session ddl locks.  For instance, try updating a table inside a transaction in one session, then drop the table inside another session, and then roll back the first session.  Likely, you'll get a "table not found" exception in the first transaction, whereas IMO it would probably be better to raise an exception in the second session, stating that one cannot drop or alter the table, because it is in use by another session.
>----- Original Message -----
>From: Irum Godil <softwarengineer2004@...>
>Date: Friday, March 11, 2005 11:06 pm
>Subject: [Hsqldb-developers] Transaction Commit
>
>  
>
>>Hi, 
>>I am going through the Hsql Database code, and I noticed that in 
>>the class DatabaseCommandInterpreter the call to Session.commit is 
>>happening in a few places. One such example is in method: 
>>
>>private void processCreateTable(int type) throws HsqlException {...}
>>
>>I see that if I have created a table, done some transactions on the 
>>table, and now create a new table; then all the transactions made 
>>on the first table are committed by call to function: 
>>
>>Session.Commit( )
>>
>>I do not understand why is this being done. If I have turned 
>>AutoCommit to false, then I do not want any commits to happen until 
>>I explicitly call commit( ) right? So, if some transactions are 
>>being committed, then is not that wrong. How is atomicity of 
>>transactions preserved in such a case?
>>
>>Thanks. 
>>
>>
>>
>>
>>       	
>>---------------------------------
>>Do you Yahoo!?
>>Yahoo! Small Business - Try our new resources site! 
>>    
>>
>
>
>
>-------------------------------------------------------
>SF email is sponsored by - The IT Product Guide
>Read honest & candid reviews on hundreds of IT Products from real users.
>Discover which products truly live up to the hype. Start reading now.
>http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
>_______________________________________________
>hsqldb-developers mailing list
>hsqldb-developers@...
>https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>  
>

Re: [Hsqldb-developers] Transaction Commit

[fredt <fredt@us...>]

Yes Campbell,  the enhancement you suggest is being implemented for 1.8.0

Fred

----- Original Message ----- 
From: "Lorna Burnet" <lornabb2@...>
To: <hsqldb-developers@...>
Sent: 12 March 2005 22:24
Subject: Re: [Hsqldb-developers] Transaction Commit


It happens because in hsqlsb, currently, data definition language execution 
is not "transactional"

That is, you cannot roll back table creation, etc.

So, if you issue ddl in the middle of a transaction, this is equivalent to 
an implicit commit.

The same is true of many DBMS, including Oracle, MS SQL Server, etc.  I 
can't say off the top of my head, but I'd bet the farm that the same applies 
to Postgresql, MySQL, etc.  In fact, I think you'd have to really search 
carefully to find the small handfull of systems that actually support ddl 
operations as undoable transaction items.


Where HSQLDB really needs work regarding ddl is not to support undoable 
itms, but rather to support cross-session ddl locks.  For instance, try 
updating a table inside a transaction in one session, then drop the table 
inside another session, and then roll back the first session.  Likely, 
you'll get a "table not found" exception in the first transaction, whereas 
IMO it would probably be better to raise an exception in the second session, 
stating that one cannot drop or alter the table, because it is in use by 
another session.
----- Original Message -----
From: Irum Godil <softwarengineer2004@...>
Date: Friday, March 11, 2005 11:06 pm
Subject: [Hsqldb-developers] Transaction Commit

>
> Hi,
> I am going through the Hsql Database code, and I noticed that in
> the class DatabaseCommandInterpreter the call to Session.commit is
> happening in a few places. One such example is in method:
>
> private void processCreateTable(int type) throws HsqlException {...}
>
> I see that if I have created a table, done some transactions on the
> table, and now create a new table; then all the transactions made
> on the first table are committed by call to function:
>
> Session.Commit( )
>
> I do not understand why is this being done. If I have turned
> AutoCommit to false, then I do not want any commits to happen until
> I explicitly call commit( ) right? So, if some transactions are
> being committed, then is not that wrong. How is atomicity of
> transactions preserved in such a case?
>
> Thanks.
>
>
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site!



-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers