Re: [h2] MVStore incrementVersion and commit throws "IllegalArgumentException: Unknown version"

[Ashwin Jayaprakash]

Looks good with some minor comments:

I think close() should do a commit on any uncommited data by default... 
unless the session has already been rolled back.

And close() should probably also return the last committed version for 
logging/diagnostic purposes. So if the session rolled back everything 
before calling close, it just return the last committed version of the 
store.



On Sunday, October 27, 2013 11:12:30 PM UTC-7, Thomas Mueller wrote:
>
> Hi,
>
> What about:
>
> - commit(): also stores the changed (except for in-memory stores)
> - rollbackTo(): keep
> - store(), incrementVersion(), getCommittedVersion(): remove
> - close(): just close the file without storing (same as closeImmediately 
> is doing now)
> - closeImmediately(): remove
> - hasUnsavedChanges(): rename to hasUncommittedChanges()
>
> That means for persistent stores, commit and store is the same.
>
> In addition to that, I think I will add an autoCommit feature, probably 
> even enabled by default, because it looks like it's quite hard for an 
> application to decide when to best call commit. But in this case, close() 
> should also store the uncommitted changes.
>
> What do you think?
>
> Regards,
> Thomas
>
>
>
> On Sunday, October 27, 2013, Ashwin Jayaprakash wrote:
>
>> I suppose you just need commit and commitAndClose. Otherwise you just end 
>> up answering questions about various combinations on the mailing list :)
>>
>>
>> On Saturday, October 26, 2013 12:11:50 AM UTC-7, Thomas Mueller wrote:
>>>
>>> Hi,
>>>
>>> > I have 1 suggestion/doubt - why is incrementVersion() even exposed? 
>>> Is it meant to be a checkpoint for uncommitted transactions? It's confusing.
>>>
>>> Yes, it is a checkpoint for uncommitted transactions. But you are right, 
>>> it is confusing, and it is not strictly needed, as one could use commit and 
>>> then rollback to the previous version if needed.
>>>
>>> I think I will remove this feature, and probably also remove or change 
>>> some related features that are questionable. The whole concept of 
>>> "uncommitted but saved changes" for example, I'm not quite sure it that 
>>> still makes sense. Also, I wonder if MVStore.close() should store anything, 
>>> or whether it should be replaced with closeImmediately(). If those features 
>>> are needed by an application, it would still be possible to implement them 
>>> on top of the MVStore. As far as I see, I don't need them for the database 
>>> engine itself.
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>> On Fri, Oct 25, 2013 at 7:08 PM, Ashwin Jayaprakash <
>>> ashwin.ja...@gmail.com> wrote:
>>>
>>> I have 1 suggestion/doubt - why is incrementVersion() even exposed? Is 
>>> it meant to be a checkpoint for uncommitted transactions? It's confusing.
>>>
>>> Why would I not use commit() instead to bump up the version? Because it 
>>> has to flush the store which might be slower than just increment?
>>>
>>> Can multiple threads access store concurrently? Can multiple threads 
>>> access diff versions of a map concurrently?
>>>
>>> Thanks.
>>>
>>>
>>>
>>>
>>> On Friday, October 25, 2013 4:51:19 AM UTC-7, Thomas Mueller wrote:
>>>
>>> Hi,
>>>
>>> Thanks a lot for reporting! This is a very good test case. I can 
>>> reproduce the problem now, I will try to fix it in the next days. The 
>>> problem isn't really the number of entries in the map, the problem is that 
>>> the background thread stores the transient (uncommitted) changes to avoid 
>>> out of memory, and closing will try to revert that (and this is what fails 
>>> for some reason).
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>> On Fri, Oct 25, 2013 at 7:33 AM, Ashwin Jayaprakash <
>>> ashwin.ja...@gmail.com> wrote:
>>>
>>> I was trying to run this simple MVStore test, essentially trying to see 
>>> if the multiple read-only versions worked. 
>>>
>>> I noticed that if the commit() statements are not called after an 
>>> incrementVersion(), then close() throws an unknown version exception. See 
>>> highlighted sections below in the code. What am I doing wrong?
>>>
>>> *Exception:
>>> *
>>> Exception in thread "main" java.lang.**IllegalArgumentExcep**tion: 
>>> Unknown version 2 [1.3.174/0]
>>>   

Re: [h2] MVStore incrementVersion and commit throws "IllegalArgumentException: Unknown version"

[Ashwin Jayaprakash]

I suppose you just need commit and commitAndClose. Otherwise you just end 
up answering questions about various combinations on the mailing list :)


On Saturday, October 26, 2013 12:11:50 AM UTC-7, Thomas Mueller wrote:
>
> Hi,
>
> > I have 1 suggestion/doubt - why is incrementVersion() even exposed? Is 
> it meant to be a checkpoint for uncommitted transactions? It's confusing.
>
> Yes, it is a checkpoint for uncommitted transactions. But you are right, 
> it is confusing, and it is not strictly needed, as one could use commit and 
> then rollback to the previous version if needed.
>
> I think I will remove this feature, and probably also remove or change 
> some related features that are questionable. The whole concept of 
> "uncommitted but saved changes" for example, I'm not quite sure it that 
> still makes sense. Also, I wonder if MVStore.close() should store anything, 
> or whether it should be replaced with closeImmediately(). If those features 
> are needed by an application, it would still be possible to implement them 
> on top of the MVStore. As far as I see, I don't need them for the database 
> engine itself.
>
> Regards,
> Thomas
>
>
> On Fri, Oct 25, 2013 at 7:08 PM, Ashwin Jayaprakash <
> ashwin.ja...@gmail.com > wrote:
>
>> I have 1 suggestion/doubt - why is incrementVersion() even exposed? Is it 
>> meant to be a checkpoint for uncommitted transactions? It's confusing.
>>
>> Why would I not use commit() instead to bump up the version? Because it 
>> has to flush the store which might be slower than just increment?
>>
>> Can multiple threads access store concurrently? Can multiple threads 
>> access diff versions of a map concurrently?
>>
>> Thanks.
>>
>>
>>
>>
>> On Friday, October 25, 2013 4:51:19 AM UTC-7, Thomas Mueller wrote:
>>
>>> Hi,
>>>
>>> Thanks a lot for reporting! This is a very good test case. I can 
>>> reproduce the problem now, I will try to fix it in the next days. The 
>>> problem isn't really the number of entries in the map, the problem is that 
>>> the background thread stores the transient (uncommitted) changes to avoid 
>>> out of memory, and closing will try to revert that (and this is what fails 
>>> for some reason).
>>>
>>> Regards,
>>> Thomas
>>>
>>>
>>> On Fri, Oct 25, 2013 at 7:33 AM, Ashwin Jayaprakash <
>>> ashwin.ja...@gmail.com> wrote:
>>>
>>>> I was trying to run this simple MVStore test, essentially trying to see 
>>>> if the multiple read-only versions worked. 
>>>>
>>>> I noticed that if the commit() statements are not called after an 
>>>> incrementVersion(), then close() throws an unknown version exception. See 
>>>> highlighted sections below in the code. What am I doing wrong?
>>>>
>>>> *Exception:
>>>> *
>>>> Exception in thread "main" java.lang.**IllegalArgumentException: 
>>>> Unknown version 2 [1.3.174/0]
>>>> at org.h2.mvstore.DataUtils.**newIllegalArgumentException(**
>>>> DataUtils.java:672)
>>>> at org.h2.mvstore.DataUtils.**checkArgument(DataUtils.java:**659)
>>>> at org.h2.mvstore.MVStore.**rollbackTo(MVStore.java:1749)
>>>> at org.h2.mvstore.MVStore.close(**MVStore.java:678)
>>>> at test.MvTest1.main(MvTest1.**java:54)
>>>>
>>>> *Code:
>>>> *
>>>> public class MvTest1 {
>>>> public static void main(String[] args) {
>>>> MVStore s = new MVStore.Builder().
>>>> backgroundExceptionHandler(
>>>> new UncaughtExceptionHandler() {
>>>> @Override
>>>> public void uncaughtException(Thread 
>>>> thread, Throwable throwable) {
>>>>   **  throwable.printStackTrace();
>>>> }
>>>> }).
>>>> cacheSize(10).
>>>> compressData().
>>>> encryptionKey("007".**toCharArray()).
>>>> fileStore(new OffHeapStore()).
>>>> pageSplitSize(6 * 1024).
>>>> readOnly().
>>>> writeBufferSize(8).
>>>> writeDelay(100).
>>>> open();
>>>>
>>>>  

Re: [h2] MVStore incrementVersion and commit throws "IllegalArgumentException: Unknown version"

[Ashwin Jayaprakash]

I have 1 suggestion/doubt - why is incrementVersion() even exposed? Is it 
meant to be a checkpoint for uncommitted transactions? It's confusing.

Why would I not use commit() instead to bump up the version? Because it has 
to flush the store which might be slower than just increment?

Can multiple threads access store concurrently? Can multiple threads access 
diff versions of a map concurrently?

Thanks.



On Friday, October 25, 2013 4:51:19 AM UTC-7, Thomas Mueller wrote:
>
> Hi,
>
> Thanks a lot for reporting! This is a very good test case. I can reproduce 
> the problem now, I will try to fix it in the next days. The problem isn't 
> really the number of entries in the map, the problem is that the background 
> thread stores the transient (uncommitted) changes to avoid out of memory, 
> and closing will try to revert that (and this is what fails for some 
> reason).
>
> Regards,
> Thomas
>
>
> On Fri, Oct 25, 2013 at 7:33 AM, Ashwin Jayaprakash <
> ashwin.ja...@gmail.com > wrote:
>
>> I was trying to run this simple MVStore test, essentially trying to see 
>> if the multiple read-only versions worked. 
>>
>> I noticed that if the commit() statements are not called after an 
>> incrementVersion(), then close() throws an unknown version exception. See 
>> highlighted sections below in the code. What am I doing wrong?
>>
>> *Exception:
>> *
>> Exception in thread "main" java.lang.IllegalArgumentException: Unknown 
>> version 2 [1.3.174/0]
>> at 
>> org.h2.mvstore.DataUtils.newIllegalArgumentException(DataUtils.java:672)
>> at org.h2.mvstore.DataUtils.checkArgument(DataUtils.java:659)
>> at org.h2.mvstore.MVStore.rollbackTo(MVStore.java:1749)
>> at org.h2.mvstore.MVStore.close(MVStore.java:678)
>> at test.MvTest1.main(MvTest1.java:54)
>>
>> *Code:
>> *
>> public class MvTest1 {
>> public static void main(String[] args) {
>> MVStore s = new MVStore.Builder().
>> backgroundExceptionHandler(
>> new UncaughtExceptionHandler() {
>> @Override
>> public void uncaughtException(Thread thread, 
>> Throwable throwable) {
>> throwable.printStackTrace();
>> }
>> }).
>> cacheSize(10).
>> compressData().
>> encryptionKey("007".toCharArray()).
>> fileStore(new OffHeapStore()).
>> pageSplitSize(6 * 1024).
>> readOnly().
>> writeBufferSize(8).
>> writeDelay(100).
>> open();
>>
>> //Put some items.
>> MVMap names = s.openMap("names");
>> for (int i = 0; i < 1; i++) {
>> names.put(System.nanoTime() + "_" + i, 
>> Long.toString(names.sizeAsLong()));
>> }
>> System.out.println("Map count: " + names.size() + " version: " + 
>> names.getVersion());
>>
>> //Take a snapshot.
>> MVMap namesOld = 
>> names.openVersion(names.getVersion());
>> //Bump up the version.
>> s.incrementVersion();
>> // Either both this commit and the one below MUST be called OR 
>> NEITHER!
>> // s.commit();
>>
>> //Put some more items.
>> for (int i = 2; i < 25000; i++) {
>> names.put(System.nanoTime() + "_" + i, 
>> Long.toString(names.sizeAsLong()));
>> }
>> System.out.println("Map count: " + names.size() + " version: " + 
>> names.getVersion());
>> System.out
>> .println("Old map count: " + namesOld.size() + " version: 
>> " + namesOld.getVersion());
>>
>> //s.commit();
>> s.close();
>> }
>> }
>>
>> Regards,
>> Ashwin.
>>
>>  -- 
>> You received this message because you are subscribed to the Google Groups 
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to h2-database...@googlegroups.com .
>> To post to this group, send email to h2-da...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[h2] MVStore incrementVersion and commit throws "IllegalArgumentException: Unknown version"

[Ashwin Jayaprakash]

I was trying to run this simple MVStore test, essentially trying to see if 
the multiple read-only versions worked. 

I noticed that if the commit() statements are not called after an 
incrementVersion(), then close() throws an unknown version exception. See 
highlighted sections below in the code. What am I doing wrong?

*Exception:
*
Exception in thread "main" java.lang.IllegalArgumentException: Unknown 
version 2 [1.3.174/0]
at 
org.h2.mvstore.DataUtils.newIllegalArgumentException(DataUtils.java:672)
at org.h2.mvstore.DataUtils.checkArgument(DataUtils.java:659)
at org.h2.mvstore.MVStore.rollbackTo(MVStore.java:1749)
at org.h2.mvstore.MVStore.close(MVStore.java:678)
at test.MvTest1.main(MvTest1.java:54)

*Code:
*
public class MvTest1 {
public static void main(String[] args) {
MVStore s = new MVStore.Builder().
backgroundExceptionHandler(
new UncaughtExceptionHandler() {
@Override
public void uncaughtException(Thread thread, 
Throwable throwable) {
throwable.printStackTrace();
}
}).
cacheSize(10).
compressData().
encryptionKey("007".toCharArray()).
fileStore(new OffHeapStore()).
pageSplitSize(6 * 1024).
readOnly().
writeBufferSize(8).
writeDelay(100).
open();

//Put some items.
MVMap names = s.openMap("names");
for (int i = 0; i < 1; i++) {
names.put(System.nanoTime() + "_" + i, 
Long.toString(names.sizeAsLong()));
}
System.out.println("Map count: " + names.size() + " version: " + 
names.getVersion());

//Take a snapshot.
MVMap namesOld = 
names.openVersion(names.getVersion());
//Bump up the version.
s.incrementVersion();
// Either both this commit and the one below MUST be called OR 
NEITHER!
// s.commit();

//Put some more items.
for (int i = 2; i < 25000; i++) {
names.put(System.nanoTime() + "_" + i, 
Long.toString(names.sizeAsLong()));
}
System.out.println("Map count: " + names.size() + " version: " + 
names.getVersion());
System.out
.println("Old map count: " + namesOld.size() + " version: " 
+ namesOld.getVersion());

//s.commit();
s.close();
}
}

Regards,
Ashwin.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Well.., it is PostGres for the prototype. It could very easily be a
different backend store too. There is quite of bit of code there that
provides the features we need sitting between H2 and PG. We'll see how it
goes.

Thanks.


On Mon, Jun 17, 2013 at 3:06 AM, Noel Grandin  wrote:

> Ah, I see.
> Well, we can handle large in-memory tables via our experimental "nio:"
> storage layer, but not streaming replication.
>
> So you're obviously building something kind of streaming storage
> product on top of H2.
>
> Which is fine, I don't have a problem with that, but I still agree
> with Thomas - we're not intending to expose our SQL parsing
> infrastructure as an API, so we're not really interested in making
> your particular use-case easier.
>
> Sorry about that, wish you the best with your product.
>
> On the other hand, if you were going to contribute such a storage
> engine back to the core H2 community, then we'd be interested in
> integrating your patches.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [h2] Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Can it handle 250+GB and do streaming replication?


On Thu, Jun 13, 2013 at 11:35 PM, Noel Grandin wrote:

>
> On 2013-06-13 23:08, Ashwin Jayaprakash wrote:
>
>> Thomas, I do understand your point of view. However, our intent is to use
>> the AST and custom TableEngine to host in-memory tables without data in
>> them - transient tables/views.
>>
> We already support pure in-memory tables, what makes your different?
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[h2] Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Thomas, I do understand your point of view. However, our intent is to use 
the AST and custom TableEngine to host in-memory tables without data in 
them - transient tables/views.

The larger use case requires many other parts that are not H2. Our goal was 
to use H2 for its great, functional database interface, but the data and 
logic would be elsewhere. I hope you understand why we are unable to 
contribute that back to H2 code. 

I believe that making the AST available/visible would allow some people to 
use H2 as a "kernel" in certain products. Just like the TableEngine API, if 
you were to accept this patch, given time, more people would be able to add 
value/contribute to the core if it is "open" and not an internal API.

Other people who need this or built this using Derby or hand built. IMO H2 
would've been a cleaner, lighter weight, overall nicer thing to embed - if 
it were possible:

   - https://github.com/akiban/sql-parser
   - 
   
http://apache-database.10148.n7.nabble.com/SQL-Parser-Code-in-Apache-Derby-Source-Code-td105458.html
   - http://rickosborne.org/blog/2010/02/derby-svn-coldfusion-sql-parser/
   - https://www.google.com/search?q=derby+sql+parser
   

Ashwin.

I don't currently see the use case. Why would you want to manipulate the 
> AST _outside_ of the database? It sounds like you want to add more features 
> to the database, but don't want to actually contribute them to the database 
> itself. That sounds wrong.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Hi, I managed to setup the H2 Dev environment (really easy, hat tip to 
Thomas) and patch the org.h2.command.dml.Select class to address the gaps I 
was talking about earlier.

Pls review the patch attached with a simple test class. It has some dead 
simple getter method fixes. If this looks good, I hope you guys can 
integrate it with the H2 Trunk.


Thanks!

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


Index: src/main/org/h2/command/dml/TestSelectAstFixes.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>windows-1252
===
--- src/main/org/h2/command/dml/TestSelectAstFixes.java	(revision )
+++ src/main/org/h2/command/dml/TestSelectAstFixes.java	(revision )
@@ -0,0 +1,96 @@
+package org.h2.command.dml;
+
+import org.h2.Driver;
+import org.h2.command.Parser;
+import org.h2.engine.Session;
+import org.h2.expression.Expression;
+import org.h2.expression.Subquery;
+import org.h2.jdbc.JdbcConnection;
+import org.h2.table.Table;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+
+/*
+ * Author: Ashwin Jayaprakash / Date: 4/22/13 / Time: 4:40 PM
+ */
+public class TestSelectAstFixes {
+public static void main(String[] args) throws SQLException {
+Driver.load();
+Connection c = DriverManager.getConnection("jdbc:h2:mem:test");
+
+Statement s = c.createStatement();
+
+s.execute("create table if not exists people_crm" +
+" (crm_id varchar(256) primary key, id varchar(256), name varchar(256), age int)");
+
+JdbcConnection h2c = (JdbcConnection) c;
+Session session = (Session) h2c.getSession();
+Parser parser = new Parser(session);
+
+testQueries(parser);
+}
+
+private static void testQueries(Parser parser) {
+String q = "select distinct name, count(*), min(age), max(age)/3, upper(name)" +
+" from people_crm" +
+" where age < 100" +
+" group by name" +
+" having count(*) > 100 and name like '%'" +
+" order by upper(name)" +
+" limit 5 offset 1";
+parseAndPrint(parser, q);
+
+System.out.println("--");
+q = "select * from people_crm";
+parseAndPrint(parser, q);
+
+System.out.println("--");
+q = "select p1.age, p2.age from people_crm as p1, people_crm as p2 where p1.age < p2.age";
+parseAndPrint(parser, q);
+
+System.out.println("--");
+q = "select age, count(*) from (select * from people_crm where name like 'abc%') as q where q.age > 10";
+parseAndPrint(parser, q);
+}
+
+private static void parseAndPrint(Parser parser, String q) {
+Subquery subquery = (Subquery) parser.parseExpression(q);
+
+Select select = (Select) subquery.getQuery();
+
+java.util.Set tables = select.getTables();
+boolean distinct = select.isDistinctQuery();
+List projections = select.getProjections();
+Expression where = select.getCondition();
+List groups = select.getGroupBy();
+Expression having = select.getHaving();
+List sorts = select.getOrderList();
+Expression limit = select.getLimit();
+Expression offset = select.getOffset();
+
+System.out.println("Testing:\n" + q);
+
+System.out.println("\nFull AST:\n" + select.getPlanSQL());
+
+System.out.println("\nComponents:");
+System.out.println("Tables:\n" + tables);
+System.out.println("\nDistinct:\n" + distinct);
+System.out.println("\nProjections:\n" + projections);
+System.out.println("\nWhere:\n" + where);
+System.out.println("\nGroups:\n" + groups);
+System.out.println("\nHaving:\n" + having);
+System.out.print("\nSorts:\n");
+if (sorts != null) {
+for (SelectOrderBy sort : sorts) {
+System.out.print(sort.getSQL() + " ");
+}
+System.out.println();
+}
+System.out.println("\nLimit:\n" + limit);
+System.out.println("\nOffset:\n&quo

Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

 I tried the following using h2-*1.3.171*.jar. Here's what I found for this 
query: select name, count(*) from people_crm where age < 45 group by name 
having count(*) > 100 order by name


   1. Although I see the getGroupBy, getHaving methods, they return null
   2. I see a condition field inside Select but there is no getter method
   3. There is no getOrderBy method
   
2 images attached from the debug session. What am I missing?

Thanks.




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

Re: Exposing some getter methods and classes for TableEngine implementors

[Ashwin Jayaprakash]

I have something to add to my previous request. For a moderately complex 
query like this:

select *a*.id, *a*.age, b.id, *c*.id, *c***.type
from *a* 
  left outer join *b* on a.id = b.id
  left outer join *c* on b.id = c.id 
where *a*.age < 42 and *c*.type = 'answers'

Here, a.id, b.id, c.id are all indexed columns.

While executing this query, the database does a nested loop join of:
   a (age < 42) *[* b (b.id = current a.id) *[* c (c.id = current b.id) *]* 
*]*

*Issue 1: *This seems alright except for the deepest join on c where the 
predicate (c.type = 'answers') *does not get pushed down* in the 
TableFilter.getFilterCondition() for "c" where it is null.

*Issue 2:* The second thing is more of an enhancement request where the *
TableFilter.fullCondition* does not have a *getter method*. It would be 
nice if we had one for that. If this were available then issue 1 could be 
somewhat alleviated if we had visibility into the overall/full condition. 
This way TableEngine implementors can do some early optimizations.

Thanks!

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

Exposing some getter methods and classes for TableEngine implementors

[Ashwin Jayaprakash]

Hi, this is a followup to a request I made a few days ago - "Get methods on 
limitExpr, offsetExpr, distinct in 
...". 
I'm very pleased to say that I got instant resolution on that, Thanks Noel 
Grandin!

*For custom implementations of TableEngine and related Table/Index classes:* 

   - Delete:
  - This normally translates to a fetch cursor (with or without index) 
  and then call Table.removeRow(xx) on the filtered rows
   - Update:
   - An UPDATE will translate to a fetch cursor and call 
  Table.updateRows(xx) with the affected rows
  - This then translates eventually to the default Table.removeRow(xx) 
  and Table.addRow(xx) behavior for each row
   - Some cases can exploit early optimizations on UPDATE and DELETE 
   statements
  - This "may" be done by *short circuiting* these fetch-cursor and 
  then delete+add steps in some cases by snooping into the *
  session.getCurrentCommand()*
  - To accomplish this I feel that the following minor enhancements 
  need to be done
  
*Enhancement request:*
1) org.h2.command.CommandContainer:
  Make class public
  Add getter for "prepared"

2) org.h2.command.dml.Update:
  Add getter for "condition"
  Add getter for "tableFilter"
  Add getter for "columns"
  Add getter for "expressionMap"

3)  org.h2.command.dml.Delete
  Add getter for "condition"
  Add getter for "tableFilter"

Hope this makes sense.

Thanks,
Ashwin.

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

Re: Table with custom TableEngine does not receive "alter table xx add column xx" calls

[Ashwin Jayaprakash]

Thanks Thomas!

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

Re: Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Many thanks!

On Wednesday, February 6, 2013 11:12:46 PM UTC-8, Noel Grandin wrote:
>
> sure, no problem. 
> Done. 
>
>

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

Get methods on limitExpr, offsetExpr, distinct in org.h2.command.dml.Query (and more)

[Ashwin Jayaprakash]

Hi, I see that Query/Select DML classes expose a lot of useful getter 
methods on the parsed SQL SELECT statement. 

For completeness I was wondering if I could persuade you to expose the 
limit/offset/distinct/having/group expressions - just public getter methods.

This is for people who are implementing their own TableEngines. It would be 
nice to peek into the parsed statement and do some extra optimizations 
based on the query issued.

Thanks!

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

Re: Table with custom TableEngine does not receive "alter table xx add column xx" calls

[Ashwin Jayaprakash]

That's not the problem here. The DB does not re-create or pass along the 
"ALTER TABLE" command. If the DB were to pass those calls to the engine's 
create() method then I can easily do it.

The problem here is that the alter call bypasses the custom engine and a 
new table gets created as a default H2 table! That is definitely a bug.

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

Table with custom TableEngine does not receive "alter table xx add column xx" calls

[Ashwin Jayaprakash]

Hi, I tried creating an in-memory ("jdbc:h2:mem:demo") table with a custom 
storage engine. The "createTable(CreateTableData data)" gets called. 
Everything ok.

Version used: H2 1.3.170

Now, when I issue an alter table call on that table, the table engine does 
not get notified. I tried debugging and 
"AlterTableAlterColumn.cloneTableStructure" seems to be cloning the 
original table without the "tableEngine" parameter. 

It looks like a bug to me as the TableEngine should be called again.

Thanks,
Ashwin. 

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

Re: Incorrect SpatialKeys returned by MVRTreeMap.findIntersectingKeys() in h2-1.3.170?

[Ashwin Jayaprakash]

Thanks. Will try it out.

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

Incorrect SpatialKeys returned by MVRTreeMap.findIntersectingKeys() in h2-1.3.170?

[Ashwin Jayaprakash]

Hi, I was trying to run a simple test using the in-mem MVRTreeMap and I 
added a 1001 keys and values into the map.

I then tried querying the intersection for a given key. The returned cursor 
seems to have keys where the "id" part is 0. Sometimes it returns the 
correct id in the SpatialKey - esp for 100 sized maps but not for most keys 
in the larger maps.

As a result, when I use the current key in the iterator to get the value 
from the map, it returns null.

The strange part is that both the keyList() and values() both match the 
total size(). The number of results are correct but just not the data in 
the returned SpatialKeys from the iterator.

Or am I doing something wrong?

*Code:*
public class Mvs {
public static void main(String[] args) {
MVStore s = MVStore.open(null);

MVRTreeMap r = MVRTreeMap.create(1, new ObjectType());

r = s.openMap("data", r);

r.add(new SpatialKey(0, -1f, 10f), "zero");
r.add(new SpatialKey(1, 3f, 5f), "one");
r.add(new SpatialKey(2, -3f, -2f), "two");
r.add(new SpatialKey(3, 300f, 5000f), "three");
r.add(new SpatialKey(4, -300f, 300f), "four");
for (int i = 5; i < 1000; i++) {
r.add(new SpatialKey(i, (float) i, (float) i + 10), (i + "_i"));
}
r.add(new SpatialKey(55545464563L, -300f, 300f), "big num");

System.out.println(r.size());
List keys = r.keyList();
System.out.println(keys.size());
System.out.println(keys);
Collection values = r.values();
System.out.println(values.size());
System.out.println(values);

System.out.println();
Iterator it = r.findIntersectingKeys(new SpatialKey(0, 
55f, 505f));
for (SpatialKey k; it.hasNext(); ) {
k = it.next();
System.out.println(k + " = " + r.get(k));
}

s.close();
}
}

*Sample output:*
...
0: (99.0/109.0) = null
0: (98.0/108.0) = null
0: (97.0/107.0) = null
0: (96.0/106.0) = null
...
55545464563: (-300.0/300.0) = big num


Thanks!

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

H2 on Linux RAM drive (/dev/shm)?

[Ashwin Jayaprakash]

This is related to running H2 in-memory.

Out of curiosity, has anyone used H2 database disk stored on /dev/shm? This 
built-in RAM drive (shared memory) is part of the 2.6+ Linux Kernel builds 
and is available on most Linux distros. This would probably be more scalable 
in terms of DB size than using the H2 in-memory driver - as the data will be 
out of the JVM heap but still in the OS memory.

Info on /dev/shm + Java: 
http://javaforu.blogspot.com/2011/09/ram-disk-is-already-in-linux-and-nobody.html

Ashwin.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/h2-database/-/l61nP0gbLqEJ.
To post to this group, send email to h2-database@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: Plan rewriting for TableFilter

[Ashwin Jayaprakash]

I'll try to get some test in a few weeks' time. 

In the meanwhile, look at this - 
http://gridgain.blogspot.com/2011/01/taste-of-sql-for-in-memory-cache.html. 
Sending the complete (including non-indexed expressions) filter to the 
remote nodes might be of use here.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-database@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: Index Support in H2

[Ashwin Jayaprakash]

Read the docs - http://www.h2database.com/html/grammar.html#create_index 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-database@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: Plan rewriting for TableFilter

[Ashwin Jayaprakash]

Ok let's take a simpler example: 

create table foo(name varchar(50) primary key, age int);

insert into foo values('abc', 10);
insert into foo values('def', 11);
insert into foo values('ghi', 12);

select * from foo where age > 10

Since there is no index on "age", the engine runs a table scan, gets the 
results as a Cursor and then filters it one row at a time using "age > 10". 
If only the filter were visible to the cursor, then the cursor could've done 
some optimization by pre-filtering "age > 10" - a.k.a Predicate Pushdown.

See the stacktrace here? The filter (fullCondition field) is there in 
TableFilter but not visible to IndexCursor in the callstack. If it can be 
passed to the IndexCursor in the Session or some other mechanism then custom 
indexes can make data fetching faster.



-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To post to this group, send email to h2-database@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.

Plan rewriting for TableFilter

[Ashwin Jayaprakash]

Hi, I have a query that looks like this:
select * from foo, bar
where foo.name = bar.name
and foo.age > 10
order by name

The explain plan shows this:
SELECT FOO.NAME, FOO.AGE, BAR.NAME, BAR.COUNTRY
FROM PUBLIC.FOO /* PUBLIC.PRIMARY_KEY_1 */ /* WHERE FOO.AGE > 10 */
INNER JOIN PUBLIC.BAR /* PUBLIC.BAR.tableScan */ ON 1=1
WHERE (FOO.AGE > 10) AND (FOO.NAME = BAR.NAME)
ORDER BY 1
/* index sorted */

The plan looks perfectly ok. There is a primary key on FOO.NAME. I have a 
custom index that allows a table scan on the primary key index because it's 
all in memory - that's why you see primary_key_1 instead of tablescan on 
FOO.

Anyway, on the FOO TableFilter I want to do predicate-push down of the "WHERE 
FOO.AGE > 10" filter into the tablescan itself. Instead, what happens now in 
H2 is the TableFilter uses IndexCursor which fetches everything and then 
does a post-filter of "WHERE FOO.AGE > 10". TableFilter has the 
filterCondition but it is not passing it to IndexCursor and hence the Index. 
It would've been nice if the indexes and filters were aware of the items in 
the call stack so that my custom index could use the filter hint without my 
having to change the H2 source code (Thomas? Are you listening? :-) )

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.

What is the purpose of Index.getRow(session, key)?

[Ashwin Jayaprakash]

I'm looking at the Index source and its various implementations. I'm not 
sure I understand the need for Row getRow(Session session, long key).

Index already has Cursor findXX() methods. If there is already a Primary key 
index would this method be used/required? So, what is the long key method 
for? Who generates the long ids and is it mandatory even if I implement a 
custom Table?

Also, I realized that most of the fields in RegularTable and almost 
everywhere else are private level. It's not possible to sub-class these in a 
meaningful way. These major classes have so much logic that sub-classes 
could have reused.

Regards,
Ashwin.

-- 
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.