Re: Insert Performance In Embedded application

[Sergi Vladykin]

Hi,
1) try HashSet instead of List for id storing (for faster lookups);
2) try to use MERGE command instead of seeking ids in your collection
(instead of previous suggestion)
3) try PRIMARY KEY HASH (it also uses HashMap for lookups);
4) try to add heap memory as much as possible (to decrease GC
pressure)

If this still too slow look at TableEngine api to implement your own
table.

regards,
Sergi

On 19 авг, 02:28, thebbk  wrote:
> I am using H2 as an embedded database in a Java 1.6 application. The
> Database is in-memory. I create a single table using 'CREATE MEMORY
> TABLE TestTable'. I have an index on what we call our ID field and put
> this at the end of the table creation sql: 'PRIMARY KEY (id)'
>
> The purpose of the application is to receive objects from a type of
> stream. Each object is converted into an INSERT, UPDATE or DELETE sql
> statement depending on whether or not the object (record) exists. I
> use a java.util.List to hold the list of ID's in the database - so I
> don't have to query the database every time to see if the record
> exists.
>
> The rate at which these objects are being received from the stream is
> fairly high, and there are 41 fields of varchar's and numerics.
>
> Essentially, the application takes the objects from the stream and
> places them into a queue where a thread pulls each object off the
> queue and executes the sql statement on the database. Pretty straight
> forward.
>
> As the application runs, I monitor the application and see that the
> queue is always backed up to varying degrees.
>
> There are roughly 225k-250k records.
>
> Given that I have an index on our id field and the table is a memory
> table in a memory database, are there any other settings I should look
> at to try to improve the rate at which the data is inserted or updated
> in the database? Most of the activities are updates.
>
> I am using Java 1.6 and currently running this in 64-bit Java on
> Solaris 10. If there are JVM settings which might improve performance,
> I'd like to hear suggestions.
>
> Thanks!

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Insert Performance In Embedded application

[thebbk]

I am using H2 as an embedded database in a Java 1.6 application. The
Database is in-memory. I create a single table using 'CREATE MEMORY
TABLE TestTable'. I have an index on what we call our ID field and put
this at the end of the table creation sql: 'PRIMARY KEY (id)'

The purpose of the application is to receive objects from a type of
stream. Each object is converted into an INSERT, UPDATE or DELETE sql
statement depending on whether or not the object (record) exists. I
use a java.util.List to hold the list of ID's in the database - so I
don't have to query the database every time to see if the record
exists.

The rate at which these objects are being received from the stream is
fairly high, and there are 41 fields of varchar's and numerics.

Essentially, the application takes the objects from the stream and
places them into a queue where a thread pulls each object off the
queue and executes the sql statement on the database. Pretty straight
forward.

As the application runs, I monitor the application and see that the
queue is always backed up to varying degrees.

There are roughly 225k-250k records.

Given that I have an index on our id field and the table is a memory
table in a memory database, are there any other settings I should look
at to try to improve the rate at which the data is inserted or updated
in the database? Most of the activities are updates.

I am using Java 1.6 and currently running this in 64-bit Java on
Solaris 10. If there are JVM settings which might improve performance,
I'd like to hear suggestions.

Thanks!

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: Dropping anonymous indexes?

[Kerry Sainsbury]

What happens when you try to drop them? Do you get an error message?

This works ok for me:

create table blah(id int, name varchar);
create index on blah(name);
select INDEX_NAME from INFORMATION_SCHEMA.indexes where table_name = 'BLAH'
drop index INDEX_1  -- (where INDEX_1 is the value returned in the SELECT
statement above)

I recall having exactly this problem with MS SQL Server. Annoyed the hell
out of me!

Does anybody know if other databases support a syntax like this (or if not,
why not):

drop index on blah(name);

Cheers
Kerry

On Thu, Aug 19, 2010 at 12:13 PM, Duncan Mak  wrote:

> Hello all,
>
> Is there a way to drop indexes that were created without a given name?
> I have a test table with indexes named 'Index_4C" etc etc, and I can't
> drop them. Those indexes using "create index on TABLE(COLUMN)" and
> unnamed.
>
> Thanks.
>
> Duncan.
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-datab...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database+unsubscr...@googlegroups.com
> .
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: H2 embedded Java suggestion

[Kerry Sainsbury]

Hi rami,

On Thu, Aug 19, 2010 at 12:47 PM, Rami Ojares  wrote:

>  OMG!
> Are you seriously planning to support some other languages?
>

No, but it might be nice one day :-)


> Seriously, you guys are arguing about syntax sugaring.
>

Yes, but doesn't that matter?


> What I am really wondering is the real benefit of this embedded syntax.
> I mean it does not bring anything new.
> It just eases the deployment of some whose deployment software has
> difficulties in deploying java classes into h2's classpath.
>

Easing deployment is the "something new", and is valuable for some people.
Writing triggers or stored procedures in other databases doesn't require the
user to manually copy binary files onto the filesystem of the database
server -- that's *weird* and, IMHO, ugly.

Mind you, I think stored procedures are ugly anyway, but I guess Java ones
are less ugly than bizarre proprietary languages.

Anyway, thanks for the feedback!

Cheers
Kerry



> But maybe there is a use case I have not considered.
>
> - rami
>
>
>
> On 19.8.2010 2:56, Kerry Sainsbury wrote:
>
> I've thought about this a bit more, and wanted to advocate for including
> the module name in the CREATE MODULE command, and for ignoring the actual
> class name that will exist in the code. Therefore commands like "CREATE
> TRIGGER" will be referring to "MODULE" names, not classnames.
>
> This is primarily so we *could* in the future, if you wanted to, support
> non-Java-based syntax more easily.
>
> Agreed?
>
> Cheers
> Kerry
>
> On Thu, Aug 19, 2010 at 8:51 AM, Kerry Sainsbury wrote:
>
>> Hi Thomas,
>>
>>  On Thu, Aug 19, 2010 at 5:35 AM, Thomas Mueller <
>> thomas.tom.muel...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I think it's somewhat unlogical (at least inconsistent) that a module
>>> doesn't have a name when you create it, but has a name when you drop
>>> it. I would prefer it having a name in both cases. The name doesn't
>>> need to match the class name.
>>>
>>
>> I did start with naming the module at create time, but it seemed silly to
>> define a "name" in two places, eg:
>>
>> CREATE MODULE WIBBLE AS
>>$$
>>   import java.sql.*;
>>
>>   public class Wibble 
>>   ...
>>   $$
>>
>> Is it desirable that module vs class name could be different? DON'T REPEAT
>> YOURSELF was shouting in my ear.
>>
>> What name would you expect to use when used as part of, for example,
>> "CREATE TRIGGER" -- module or classname? If classname, then do you also want
>> the package name to be required? (Currently I've not got support for
>> packages -- they go into the existing "org.h2.dynamic" package used for
>> user-defined classes)
>>
>> CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL "WIBBLE"
>> <--- Am I calling a "module" or a "classname"?
>>
>> Perhaps the classname is completely irrelevent -- we just use whatever
>> class we happen to find defined within the 'module'?
>>
>>
>>
>>> You have used an unnamed package. Does this mean the package name can
>>> be specified in the source code. First I thought that's a bit
>>> dangerous, but it's probably not a problem (it's not a bigger security
>>> risk) and more logical that way.
>>>
>>
>> As above -- I'm thinking that there doesn't need to be any support for
>> packages.
>>
>>
>>
>>> By the way, did you see
>>> http://h2database.com/javadoc/org/h2/tools/TriggerAdapter.html ? It's
>>> very similar to SimpleTrigger - only it uses result sets instead of
>>> arrays.
>>
>>
>> I know TriggerAdapter well -- it's just that a ResultSet isn't very good
>> at letting you change values :-)
>>
>>
>>
>>> Maybe we should add a second TriggerAdapter with arrays?
>>>
>>
>> As you know, I support anything that reduces the code required to write a
>> simple trigger!
>>
>>
>> Cheers
>> Kerry
>>
>>
>>
>>
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>>
>>> On Thu, Aug 5, 2010 at 6:25 AM, Kerry Sainsbury 
>>> wrote:
>>> > Hi Thomas,
>>> >
>>> > How do you like this example. It's very simple. Basically you copy and
>>> paste
>>> > Java code and call it a "MODULE". The class can then be called from
>>> whatever
>>> > wants it (and I can easily implement my simpler trigger idea just by
>>> putting
>>> > in a "SimpleTrigger" class that I can extend in another module. Note
>>> there
>>> > is no need to name a module, as we can just use the class name:
>>> >
>>> >
>>> > CREATE MODULE AS
>>> >   $$
>>> >   import java.sql.*;
>>> >   import org.h2.api.Trigger;
>>> >
>>> >   public abstract class SimpleTrigger implements Trigger {
>>> >   public void init(Connection conn, String schemaName, String
>>> > triggerName, String tableName, boolean before, int type) throws
>>> SQLException
>>> > {
>>> >   }
>>> >   public abstract void fire(Connection conn, Object[] oldRow,
>>> > Object[] newRow) throws SQLException;
>>> >   public void close() throws SQLException {
>>> >   }
>>> >   public void remove() throws SQLException {
>>> >   }
>>> >  

Re: H2 embedded Java suggestion

[Rami Ojares]

OMG!
Are you seriously planning to support some other languages?
I mean ... wtf?!?
Seriously, you guys are arguing about syntax sugaring.
And it is no shame to place the name in a natural place from sql point 
of view.
What you are dong is mixing two languages so it is clear that there can 
arise the doubling-effect.


What I am really wondering is the real benefit of this embedded syntax.
I mean it does not bring anything new.
It just eases the deployment of some whose deployment software has 
difficulties in deploying java classes into h2's classpath.


But maybe there is a use case I have not considered.

- rami


On 19.8.2010 2:56, Kerry Sainsbury wrote:
I've thought about this a bit more, and wanted to advocate for 
including the module name in the CREATE MODULE command, and for 
ignoring the actual class name that will exist in the code. Therefore 
commands like "CREATE TRIGGER" will be referring to "MODULE" names, 
not classnames.


This is primarily so we /could/ in the future, if you wanted to, 
support non-Java-based syntax more easily.


Agreed?

Cheers
Kerry

On Thu, Aug 19, 2010 at 8:51 AM, Kerry Sainsbury > wrote:


Hi Thomas,

On Thu, Aug 19, 2010 at 5:35 AM, Thomas Mueller
mailto:thomas.tom.muel...@gmail.com>> wrote:

Hi,

I think it's somewhat unlogical (at least inconsistent) that a
module
doesn't have a name when you create it, but has a name when
you drop
it. I would prefer it having a name in both cases. The name
doesn't
need to match the class name.


I did start with naming the module at create time, but it seemed
silly to define a "name" in two places, eg:

CREATE MODULE WIBBLE AS
   $$
  import java.sql.*;

  public class Wibble 
  ...
  $$

Is it desirable that module vs class name could be different?
DON'T REPEAT YOURSELF was shouting in my ear.

What name would you expect to use when used as part of, for
example, "CREATE TRIGGER" -- module or classname? If classname,
then do you also want the package name to be required? (Currently
I've not got support for packages -- they go into the existing
"org.h2.dynamic" package used for user-defined classes)

CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL
"WIBBLE" <--- Am I calling a "module" or a "classname"?

Perhaps the classname is completely irrelevent -- we just use
whatever class we happen to find defined within the 'module'?

You have used an unnamed package. Does this mean the package
name can
be specified in the source code. First I thought that's a bit
dangerous, but it's probably not a problem (it's not a bigger
security
risk) and more logical that way.


As above -- I'm thinking that there doesn't need to be any support
for packages.

By the way, did you see
http://h2database.com/javadoc/org/h2/tools/TriggerAdapter.html
? It's
very similar to SimpleTrigger - only it uses result sets
instead of
arrays. 



I know TriggerAdapter well -- it's just that a ResultSet isn't
very good at letting you change values :-)

Maybe we should add a second TriggerAdapter with arrays?


As you know, I support anything that reduces the code required to
write a simple trigger!


Cheers
Kerry



Regards,
Thomas



On Thu, Aug 5, 2010 at 6:25 AM, Kerry Sainsbury
mailto:ke...@fidelma.com>> wrote:
> Hi Thomas,
>
> How do you like this example. It's very simple. Basically
you copy and paste
> Java code and call it a "MODULE". The class can then be
called from whatever
> wants it (and I can easily implement my simpler trigger idea
just by putting
> in a "SimpleTrigger" class that I can extend in another
module. Note there
> is no need to name a module, as we can just use the class name:
>
>
> CREATE MODULE AS
>   $$
>   import java.sql.*;
>   import org.h2.api.Trigger;
>
>   public abstract class SimpleTrigger implements Trigger {
>   public void init(Connection conn, String
schemaName, String
> triggerName, String tableName, boolean before, int type)
throws SQLException
> {
>   }
>   public abstract void fire(Connection conn,
Object[] oldRow,
> Object[] newRow) throws SQLException;
>   public void close() throws SQLException {
>   }
>   public void remove() throws SQLException {
>   }
>   }
>   $$
>
>
> CREATE MODULE AS
>   $$
>   import java.sql.*;
>
>   pub

Question about h2 test case

[JW]

Hi,

We have had database corruption problem and I try to use
org.h2.test.TestAll API to validate our system.
We use 1.2.128 version now and plan to migrate upper version.

I built 1.2.135 version with ant and copied output files to our system
and typed command below.
java -cp . org.h2.test.TestAll crash > testCrash.txt

And finally I got Exception after few hours I ran test.
Is it OK that test is finished with Exception?
I can't catch the meaning of this test. Is this a sort of stress test?

org.h2.jdbc.JdbcSQLException: General error:
"java.lang.NullPointerException" [5-135]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convert(DbException.java:279)
at org.h2.message.DbException.toSQLException(DbException.java:
252)
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:
387)
at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:236)
at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.h2.test.synth.TestCrashAPI.callRandom(TestCrashAPI.java:
333)
at org.h2.test.synth.TestCrashAPI.testOne(TestCrashAPI.java:
292)
at org.h2.test.synth.TestCrashAPI.testCase(TestCrashAPI.java:
486)
at org.h2.test.synth.TestCrashAPI.test(TestCrashAPI.java:125)
at org.h2.test.TestBase.runTest(TestBase.java:134)
at org.h2.test.TestAll.run(TestAll.java:348)
at org.h2.test.TestAll.main(TestAll.java:288)
Caused by: java.lang.NullPointerException
at org.h2.command.Parser.equalsToken(Parser.java:2601)
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:
2541)
at org.h2.command.Parser.readTableFilter(Parser.java:982)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:
1536)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1633)
at org.h2.command.Parser.parseSelectSub(Parser.java:1530)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1375)
at org.h2.command.Parser.parseSelect(Parser.java:1363)
at org.h2.command.Parser.parsePrepared(Parser.java:395)
at org.h2.command.Parser.parse(Parser.java:278)
at org.h2.command.Parser.parse(Parser.java:250)
at org.h2.command.Parser.prepareCommand(Parser.java:222)
at org.h2.engine.Session.prepareLocal(Session.java:420)
at org.h2.engine.Session.prepareCommand(Session.java:381)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1071)
at
org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:
71)
at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:234)
... 10 more
java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: General
error: "java.lang.NullPointerException" [5-135]
at org.h2.test.synth.TestCrashAPI.printError(TestCrashAPI.java:
321)
at org.h2.test.synth.TestCrashAPI.printIfBad(TestCrashAPI.java:
370)
at org.h2.test.synth.TestCrashAPI.callRandom(TestCrashAPI.java:
340)
at org.h2.test.synth.TestCrashAPI.testOne(TestCrashAPI.java:
292)
at org.h2.test.synth.TestCrashAPI.testCase(TestCrashAPI.java:
486)
at org.h2.test.synth.TestCrashAPI.test(TestCrashAPI.java:125)
at org.h2.test.TestBase.runTest(TestBase.java:134)
at org.h2.test.TestAll.run(TestAll.java:348)
at org.h2.test.TestAll.main(TestAll.java:288)
Caused by: org.h2.jdbc.JdbcSQLException: General error:
"java.lang.NullPointerException" [5-135]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convert(DbException.java:279)
at org.h2.message.DbException.toSQLException(DbException.java:
252)
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:
387)
at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:236)
at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.h2.test.synth.TestCrashAPI.callRandom(TestCrashAPI.java:
333)
... 6 more
Caused by: java.lang.NullPointerException
at org.h2.command.Parser.equalsToken(Parser.java:2601)
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:
2541)
at org.h2.command.Parser.readTableFilter(Parser.java:982)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:
1536)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1633)
at org.h2.command.Parser.parseSelectSub(Parser.ja

Dropping anonymous indexes?

[Duncan Mak]

Hello all,

Is there a way to drop indexes that were created without a given name?
I have a test table with indexes named 'Index_4C" etc etc, and I can't
drop them. Those indexes using "create index on TABLE(COLUMN)" and
unnamed.

Thanks.

Duncan.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: H2 embedded Java suggestion

[Kerry Sainsbury]

I've thought about this a bit more, and wanted to advocate for including the
module name in the CREATE MODULE command, and for ignoring the actual class
name that will exist in the code. Therefore commands like "CREATE TRIGGER"
will be referring to "MODULE" names, not classnames.

This is primarily so we *could* in the future, if you wanted to, support
non-Java-based syntax more easily.

Agreed?

Cheers
Kerry

On Thu, Aug 19, 2010 at 8:51 AM, Kerry Sainsbury  wrote:

> Hi Thomas,
>
> On Thu, Aug 19, 2010 at 5:35 AM, Thomas Mueller <
> thomas.tom.muel...@gmail.com> wrote:
>
>> Hi,
>>
>> I think it's somewhat unlogical (at least inconsistent) that a module
>> doesn't have a name when you create it, but has a name when you drop
>> it. I would prefer it having a name in both cases. The name doesn't
>> need to match the class name.
>>
>
> I did start with naming the module at create time, but it seemed silly to
> define a "name" in two places, eg:
>
> CREATE MODULE WIBBLE AS
>$$
>   import java.sql.*;
>
>   public class Wibble 
>   ...
>   $$
>
> Is it desirable that module vs class name could be different? DON'T REPEAT
> YOURSELF was shouting in my ear.
>
> What name would you expect to use when used as part of, for example,
> "CREATE TRIGGER" -- module or classname? If classname, then do you also want
> the package name to be required? (Currently I've not got support for
> packages -- they go into the existing "org.h2.dynamic" package used for
> user-defined classes)
>
> CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL "WIBBLE"
> <--- Am I calling a "module" or a "classname"?
>
> Perhaps the classname is completely irrelevent -- we just use whatever
> class we happen to find defined within the 'module'?
>
>
>
>> You have used an unnamed package. Does this mean the package name can
>> be specified in the source code. First I thought that's a bit
>> dangerous, but it's probably not a problem (it's not a bigger security
>> risk) and more logical that way.
>>
>
> As above -- I'm thinking that there doesn't need to be any support for
> packages.
>
>
>
>> By the way, did you see
>> http://h2database.com/javadoc/org/h2/tools/TriggerAdapter.html ? It's
>> very similar to SimpleTrigger - only it uses result sets instead of
>> arrays.
>
>
> I know TriggerAdapter well -- it's just that a ResultSet isn't very good at
> letting you change values :-)
>
>
>
>> Maybe we should add a second TriggerAdapter with arrays?
>>
>
> As you know, I support anything that reduces the code required to write a
> simple trigger!
>
>
> Cheers
> Kerry
>
>
>
>
>>
>> Regards,
>> Thomas
>>
>>
>>
>> On Thu, Aug 5, 2010 at 6:25 AM, Kerry Sainsbury 
>> wrote:
>> > Hi Thomas,
>> >
>> > How do you like this example. It's very simple. Basically you copy and
>> paste
>> > Java code and call it a "MODULE". The class can then be called from
>> whatever
>> > wants it (and I can easily implement my simpler trigger idea just by
>> putting
>> > in a "SimpleTrigger" class that I can extend in another module. Note
>> there
>> > is no need to name a module, as we can just use the class name:
>> >
>> >
>> > CREATE MODULE AS
>> >   $$
>> >   import java.sql.*;
>> >   import org.h2.api.Trigger;
>> >
>> >   public abstract class SimpleTrigger implements Trigger {
>> >   public void init(Connection conn, String schemaName, String
>> > triggerName, String tableName, boolean before, int type) throws
>> SQLException
>> > {
>> >   }
>> >   public abstract void fire(Connection conn, Object[] oldRow,
>> > Object[] newRow) throws SQLException;
>> >   public void close() throws SQLException {
>> >   }
>> >   public void remove() throws SQLException {
>> >   }
>> >   }
>> >   $$
>> >
>> >
>> > CREATE MODULE AS
>> >   $$
>> >   import java.sql.*;
>> >
>> >   public class MyTrigger extends SimpleTrigger {
>> >  public void fire(Connection conn, Object[] oldRow, Object[]
>> newRow)
>> > throws SQLException {
>> >  newRow[0] = ((String)newRow[0]).toUpperCase();
>> >  }
>> >   }
>> >   $$
>> >
>> >
>> > CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL
>> "MyTrigger"
>> >
>> >
>> > DROP TRIGGER TRIG_INS
>> > DROP MODULE MyTrigger
>> > DROP MODULE SimpleTrigger
>> >
>> >
>> > What do you think?
>> >
>> > Cheers
>> > Kerry
>> >
>> >
>> >
>> > On Thu, Jul 22, 2010 at 8:02 AM, Thomas Mueller
>> >  wrote:
>> >>
>> >> Hi,
>> >>
>> >> I think the
>> >> CREATE CODE MyTriggerCode LANGUAGE JAVA FOR TRIGGER AS
>> >> is very verbose. What about:
>> >> CREATE TRIGGER ... AS?
>> >> That's similar syntax as CREATE ALIAS ... AS.
>> >>
>> >> H2 only supports Java currently, so there is no reason to require
>> >> "JAVA". Once other languages are supported it can still be added as an
>> >> option.
>> >>
>> >> However I'm not sure if it makes sense to provide shortcut to directly
>> >> register a trigger as source co

Re: H2 embedded Java suggestion

[Kerry Sainsbury]

Hi Thomas,

On Thu, Aug 19, 2010 at 5:35 AM, Thomas Mueller <
thomas.tom.muel...@gmail.com> wrote:

> Hi,
>
> I think it's somewhat unlogical (at least inconsistent) that a module
> doesn't have a name when you create it, but has a name when you drop
> it. I would prefer it having a name in both cases. The name doesn't
> need to match the class name.
>

I did start with naming the module at create time, but it seemed silly to
define a "name" in two places, eg:

CREATE MODULE WIBBLE AS
   $$
  import java.sql.*;

  public class Wibble 
  ...
  $$

Is it desirable that module vs class name could be different? DON'T REPEAT
YOURSELF was shouting in my ear.

What name would you expect to use when used as part of, for example, "CREATE
TRIGGER" -- module or classname? If classname, then do you also want the
package name to be required? (Currently I've not got support for packages --
they go into the existing "org.h2.dynamic" package used for user-defined
classes)

CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL "WIBBLE"
<--- Am I calling a "module" or a "classname"?

Perhaps the classname is completely irrelevent -- we just use whatever class
we happen to find defined within the 'module'?



> You have used an unnamed package. Does this mean the package name can
> be specified in the source code. First I thought that's a bit
> dangerous, but it's probably not a problem (it's not a bigger security
> risk) and more logical that way.
>

As above -- I'm thinking that there doesn't need to be any support for
packages.



> By the way, did you see
> http://h2database.com/javadoc/org/h2/tools/TriggerAdapter.html ? It's
> very similar to SimpleTrigger - only it uses result sets instead of
> arrays.


I know TriggerAdapter well -- it's just that a ResultSet isn't very good at
letting you change values :-)



> Maybe we should add a second TriggerAdapter with arrays?
>

As you know, I support anything that reduces the code required to write a
simple trigger!


Cheers
Kerry




>
> Regards,
> Thomas
>
>
>
> On Thu, Aug 5, 2010 at 6:25 AM, Kerry Sainsbury  wrote:
> > Hi Thomas,
> >
> > How do you like this example. It's very simple. Basically you copy and
> paste
> > Java code and call it a "MODULE". The class can then be called from
> whatever
> > wants it (and I can easily implement my simpler trigger idea just by
> putting
> > in a "SimpleTrigger" class that I can extend in another module. Note
> there
> > is no need to name a module, as we can just use the class name:
> >
> >
> > CREATE MODULE AS
> >   $$
> >   import java.sql.*;
> >   import org.h2.api.Trigger;
> >
> >   public abstract class SimpleTrigger implements Trigger {
> >   public void init(Connection conn, String schemaName, String
> > triggerName, String tableName, boolean before, int type) throws
> SQLException
> > {
> >   }
> >   public abstract void fire(Connection conn, Object[] oldRow,
> > Object[] newRow) throws SQLException;
> >   public void close() throws SQLException {
> >   }
> >   public void remove() throws SQLException {
> >   }
> >   }
> >   $$
> >
> >
> > CREATE MODULE AS
> >   $$
> >   import java.sql.*;
> >
> >   public class MyTrigger extends SimpleTrigger {
> >  public void fire(Connection conn, Object[] oldRow, Object[]
> newRow)
> > throws SQLException {
> >  newRow[0] = ((String)newRow[0]).toUpperCase();
> >  }
> >   }
> >   $$
> >
> >
> > CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL
> "MyTrigger"
> >
> >
> > DROP TRIGGER TRIG_INS
> > DROP MODULE MyTrigger
> > DROP MODULE SimpleTrigger
> >
> >
> > What do you think?
> >
> > Cheers
> > Kerry
> >
> >
> >
> > On Thu, Jul 22, 2010 at 8:02 AM, Thomas Mueller
> >  wrote:
> >>
> >> Hi,
> >>
> >> I think the
> >> CREATE CODE MyTriggerCode LANGUAGE JAVA FOR TRIGGER AS
> >> is very verbose. What about:
> >> CREATE TRIGGER ... AS?
> >> That's similar syntax as CREATE ALIAS ... AS.
> >>
> >> H2 only supports Java currently, so there is no reason to require
> >> "JAVA". Once other languages are supported it can still be added as an
> >> option.
> >>
> >> However I'm not sure if it makes sense to provide shortcut to directly
> >> register a trigger as source code *snippet* (that is, only the fire
> >> method). I think it's no problem to require the source code of the
> >> *complete* class in this case. If you write short static function,
> >> then the current CREATE ALIAS ... AS source is handy (I used it many
> >> times). But if you write a trigger, you anyway do that in the IDE,
> >> meaning you anyway have the source code of the complete class
> >> somewhere. Because you want to use auto-complete of the IDE, and so
> >> forth. Triggers are almost never as simple as a Java function.
> >>
> >> I think if we want to support CREATE CODE then this should be only
> >> used to add classes to the "internal classpath" of H

Re: LOCK_MODE

[Rami]

But in JDBC api Connection interface has method
void setTransactionIsolation(int level)
So it is clearly against the JDBC api if this setting affects all the other 
connections  also.


- Rami Ojares

Thomas Mueller wrote:

Hi,


My question is this:
If I connect with url jdbc:h2:test;LOCK_MODE=0
does it also affect other connections (and transactions) or only the one
connection that set the mode
in it's url?


This is documented: "This setting affects all connections."

Regards,
Thomas



--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: Hibernate Search / Slow OR Operations

[Thomas Mueller]

Hi,

> where (key1 = ? and key2 = ?) OR (key1 = ? and key2 = ?)

H2 currently doesn't use an index for such queries. I will add a
feature request, however I'm not sure when I have time to implement an
optimization for it.

Regards,
Thomas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: [bug report] MULTI_THREADED=1 and rowCount exception

[Thomas Mueller]

Hi,

> with LOCK_MODE set to READ_UNCOMMITTED.
> N threads

This is not supported. See
http://h2database.com/html/grammar.html#set_lock_mode : "The value 0
means no locking (should only be used for testing; also known as
READ_UNCOMMITTED). Please note that using SET LOCK_MODE 0 while at the
same time using multiple connections may result in inconsistent
transactions."

> If I use READ_COMMITTED instead of READ_UNCOMMITTED, then I get some 
> deadlocks.

What kind of deadlocks? Are those Java level deadlocks, or deadlocks
because the tables are locked in different order?

> If I do not use MULTI_THREADED=1, then the performances are really bad.

How bad? Do you know why it is bad (did you profile it)?

Could you try with MVCC=TRUE instead, and disable MULTI_THREADED?

Unless it's a lot of work, I would be interested to run the test
myself. Could you upload it somewhere and post a link, or describe
what I have to change?

Regards,
Thomas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: LOCK_MODE

[Thomas Mueller]

Hi,

> My question is this:
> If I connect with url jdbc:h2:test;LOCK_MODE=0
> does it also affect other connections (and transactions) or only the one
> connection that set the mode
> in it's url?

This is documented: "This setting affects all connections."

Regards,
Thomas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: H2 embedded Java suggestion

[Thomas Mueller]

Hi,

I think it's somewhat unlogical (at least inconsistent) that a module
doesn't have a name when you create it, but has a name when you drop
it. I would prefer it having a name in both cases. The name doesn't
need to match the class name.

You have used an unnamed package. Does this mean the package name can
be specified in the source code. First I thought that's a bit
dangerous, but it's probably not a problem (it's not a bigger security
risk) and more logical that way.

By the way, did you see
http://h2database.com/javadoc/org/h2/tools/TriggerAdapter.html ? It's
very similar to SimpleTrigger - only it uses result sets instead of
arrays. Maybe we should add a second TriggerAdapter with arrays?

Regards,
Thomas



On Thu, Aug 5, 2010 at 6:25 AM, Kerry Sainsbury  wrote:
> Hi Thomas,
>
> How do you like this example. It's very simple. Basically you copy and paste
> Java code and call it a "MODULE". The class can then be called from whatever
> wants it (and I can easily implement my simpler trigger idea just by putting
> in a "SimpleTrigger" class that I can extend in another module. Note there
> is no need to name a module, as we can just use the class name:
>
>
> CREATE MODULE AS
>   $$
>   import java.sql.*;
>   import org.h2.api.Trigger;
>
>   public abstract class SimpleTrigger implements Trigger {
>   public void init(Connection conn, String schemaName, String
> triggerName, String tableName, boolean before, int type) throws SQLException
> {
>   }
>   public abstract void fire(Connection conn, Object[] oldRow,
> Object[] newRow) throws SQLException;
>   public void close() throws SQLException {
>   }
>   public void remove() throws SQLException {
>   }
>   }
>   $$
>
>
> CREATE MODULE AS
>   $$
>   import java.sql.*;
>
>   public class MyTrigger extends SimpleTrigger {
>  public void fire(Connection conn, Object[] oldRow, Object[] newRow)
> throws SQLException {
>  newRow[0] = ((String)newRow[0]).toUpperCase();
>  }
>   }
>   $$
>
>
> CREATE TRIGGER TRIG_INS BEFORE INSERT ON TEST FOR EACH ROW CALL "MyTrigger"
>
>
> DROP TRIGGER TRIG_INS
> DROP MODULE MyTrigger
> DROP MODULE SimpleTrigger
>
>
> What do you think?
>
> Cheers
> Kerry
>
>
>
> On Thu, Jul 22, 2010 at 8:02 AM, Thomas Mueller
>  wrote:
>>
>> Hi,
>>
>> I think the
>> CREATE CODE MyTriggerCode LANGUAGE JAVA FOR TRIGGER AS
>> is very verbose. What about:
>> CREATE TRIGGER ... AS?
>> That's similar syntax as CREATE ALIAS ... AS.
>>
>> H2 only supports Java currently, so there is no reason to require
>> "JAVA". Once other languages are supported it can still be added as an
>> option.
>>
>> However I'm not sure if it makes sense to provide shortcut to directly
>> register a trigger as source code *snippet* (that is, only the fire
>> method). I think it's no problem to require the source code of the
>> *complete* class in this case. If you write short static function,
>> then the current CREATE ALIAS ... AS source is handy (I used it many
>> times). But if you write a trigger, you anyway do that in the IDE,
>> meaning you anyway have the source code of the complete class
>> somewhere. Because you want to use auto-complete of the IDE, and so
>> forth. Triggers are almost never as simple as a Java function.
>>
>> I think if we want to support CREATE CODE then this should be only
>> used to add classes to the "internal classpath" of H2. Independent of
>> for what those classes are used (trigger, aggregate, function
>> alias,...), and therefore without any magic to adopt to the right type
>> / add missing glue code. Even without automatically adding import
>> statements. Those classes can then be used for many things. For
>> example a class that contains multiple public static methods plus a
>> trigger (or even multiple triggers using inner classes). Or it could
>> even contain multiple classes, or a jar file (which would be stored in
>> the database). Instead of CREATE CODE what about CREATE MODULE or
>> CREATE LIBRARY? There could be a module / library for MySQL helper
>> functions, for PostgreSQL helper functions, and so on. Like a Apache
>> HTTP module. There should be an way to "auto-start", maybe using a
>> method in the loaded library.
>>
>> Existing features could then be retro-fitted as modules / libraries.
>> For example fulltext search.
>>
>> Regards,
>> Thomas
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To post to this group, send email to h2-datab...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> h2-database+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/h2-database?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To post to this group, send email to h2-datab...@googlegroups.com.
> To unsubscribe f

Re: Error creating view

[Thomas Mueller]

Hi,

This bug will be fixed in the next release.

Regards,
Thomas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Running H2Database as a Linux Daemon

[Bruce Alspaugh]

I would like to run an H2Database server as a Linux damon process.
All I could find in the distribution were scripts and instructions to
run H2 as a Windows service using the Java Service Wrapper from Tanuki
Software.  The Windows batch scripts worked fine for me on Windows XP,
but I am not sure how to set up the scripts on Ubuntu 10.04 and I
couldn't find any in the H2 distribution.  It would seem possible to
do this because the Java Service Wrapper also supports Linux.

It would be nice if Linux daemon scripts and instructions could be
included in the distribution the same way as the Windows service
scripts.  Any advice?

Bruce

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: full text search errors

[clint]

This solution works good on my Win 7 PC, as well as my OSX 10.5 laptop


On Aug 17, 2:22 pm, Thomas Mueller 
wrote:
> Hi,
>
> > Caused by: org.apache.lucene.store.LockObtainFailedException: Lock
> > obtain timed out: simplefsl...@c:\Users\Clinton Hyde\mag-db\write.lock
>
> It looks like the Lucene index is already open, or the write.lock is
> not removed after killing the process.


or something like that (other process has it?)



>
> > if the error messages had said some of this, I'd have figured it out a lot 
> > sooner...
>
> So the problem was the object existed, but in a different schema?

that was exactly it. It was my fault that it happened that way, I
completely did not realize that was what was going on, it was mostly a
result of evolved, rather than designed, SQL, so there was some
ordering dependence that I was not recognizing--and the error msgs
were very confusing.

> This
> is not a very common problem. However I will still add a feature
> request for "If a database object was not found in the current schema,
> but one with the same name existed in another schema, included that in
> the error message."


that would have been very helpful for me :)

later today, or tomorrow, I will try this on my friend's server.


>
> Regards,
> Thomas

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.