TableEngine Example using table with multiple rows

[Atul Chowdhury]

Hi all -

 

I'm trying to flatten an object graph and have it surface through a TABLE.

 

For example, I might have a "Person" class:

 

String name = person.getName();

String phone = person.getPhone();

String add1 = person.getAddress1();

String add2 = person.getAddress2();

 

Output:

 

Joe | 333-222-4444 | 8 Rancho Driver

Joe | 333-222-4444 | Sector 22

Joe | 333-222-4444 | Palo Alto, CA

 

Via SQL, I should be able to issue a SQL query like so:

 

select NAME, PHONE, ADD1, ADD2, ADD3 from PERSONS

 

I initially thought of using user-defined functions as tables returning a ResultSet.  The problems with this were:

 

- Cannot implement filtering ("WHERE id=...") at query-time; instad the UDF retrieves the complete result set and THEN filters via WHERE

- I need to implement paging as datasets can be large

- I don't want to wrap this in a UDF as it would require syntax like "select NAME from PERSONS()". I'd rather have "select NAME from PERSONS".  If I create a view, it computes the complete dataset first (in this case it would retrieve all 500k persons and THEN provide the view) which I don't want to do.

 

So, I've decided to use the TableEngine feature (extending TableBase) - however, I'm at a loss as to the implementation details.

 

My question: To start with, how do I modify the example I've provided (see my pastebin link for code) to allow MULTIPLE rows in a custom table?  I want the 5 rows I insert to actually get stored in their entirety.  Also, if someone has a more feature-rich example, I'd love to see that as well.

 

Link to SSCCE code TableEngine Example: http://pastebin.com/qQPQWFnn

 

Thanks in advance -

 

AC

[Sergi Vladykin]

Hi,

Yes, table engine is quite an advanced feature and you just have to
understand how H2 works to implement it right. So the best example for
you so far is RegularTable and its indexes in parts where they work in
in-memory mode. Better to use them as base for your table engine
instead of this oversimplified test example.

Sergi

[Atul Chowdhury]

Hi -

 

Thanks for the pointer. 

 

I've created a very basic scenario of extending RegularTable with initial success.

 

My goal is to perform the steps below - most of which are staight-forward except STEP # 4:

 

0) H2 starts up, seeds an in-memory db with several tables that implement MyTableEngine, extending RegularTable

1) User issues a query from some query tool against H2

2) H2 BEFORE SELECT trigger fires for participating tables in query; addRow() called on MyTable to populate required data set via external java classes; possible filtering occurs on the data set prior to H2 activities etc ...

3) Data send back to client

4) H2 AFTER SELECT trigger fires and truncates MyTable tables

 

Question: How can I programmatically drop results from one of my Tables provided via TableEngine AFTER its data has been queried via SELECT ? 

 

From what I understand, I CANNOT use "AFTER SELECT".  But I'd still like a way to automatically drop results from MyTable tables.  (This may not be an issue if the session (single-connection) closes after the query completes; eg. my application's "disconnect from client" is enabled, but for now I must assume this is not guaranteed)

 

Thanks -

 

AC

 

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

[Sergi Vladykin]

May be you can copy needed data in method 'lock' and remove on
'unlock'.

Sergi

On May 21, 5:05 am, Atul Chowdhury <atulsmail...@gmail.com> wrote:
> Hi -
>
> Thanks for the pointer.
>
> I've created a very basic scenario of extending RegularTable with initial
> success.
>
> My goal is to perform the steps below - most of which are staight-forward
> except STEP # 4:
>
> 0) H2 starts up, seeds an in-memory db with several tables that implement
> MyTableEngine, extending RegularTable
> 1) User issues a query from some query tool against H2
> 2) H2 BEFORE SELECT trigger fires for participating tables in query;
> addRow() called on MyTable to populate required data set via external java
> classes; possible filtering occurs on the data set prior to H2 activities
> etc ...
> 3) Data send back to client
> 4) H2 AFTER SELECT trigger fires and truncates MyTable tables
>

> *Question:* How can I programmatically drop results from one of my Tables

> provided via TableEngine AFTER its data has been queried via SELECT ?
>
> From what I understand, I CANNOT use "AFTER SELECT".  But I'd still like a
> way to automatically drop results from MyTable tables.  (This may not be an
> issue if the session (single-connection) closes after the query completes;
> eg. my application's "disconnect from client" is enabled, but for now I
> must assume this is not guaranteed)
>
> Thanks -
>
> AC
>

[Atul]

Hi Sergi -

It's been a while since I could try this but now I've done so and I'm
not having any luck.

My flow is as follows:

- Create custom tables extending RegularTable
- Retrieve query SQL
- Build my custom dataset based on the "SQL" via RegularTable.addrow()
- OR - direct manipulation of ScanIndex.getRow() (?? not sure - still
investigating)
- Data sent back to user

If I try to close results in RegularTable.unlock() the results are
never sent to the client and are just wiped out of existence. I've
also tried tweaking things in the Session object but with no success.
I'm curious to know if you have any pointers around this. thanks
again -

Atul

> > >http://groups.google.com/group/h2-database?hl=en.- Hide quoted text -
>
> - Show quoted text -