How are "multiple rows" represented in MVStore?

[Mark Raynsford]

Hello!

I'm using MVStore to implement a persistent data store for an
application. I'm using MVStore directly because there's a requirement
that multiple processes can access the data store concurrently without
there being a mediating server process running. Thankfully there'll
only be one writer and a small number of readers.

I'm slightly confused as to how to represent a particular data
structure efficiently. It's pretty simple, so I'll describe it in terms
of SQL:

create table t (
group integer not null,
member integer not null,
primary key (group, member)
);

I think the natural way this would be expressed as a plain Java data
structure would be:

Map<Integer, Set<Integer>>

I want to execute queries such as:

select t.member from t where group = ?;

I'm not sure what the preferred way to declare the above is in terms of
the MVStore maps. The documentation says:

"In database terms, a map can be used like a table, where the key of
the map is the primary key of the table, and the value is the row."

But in this case, the primary key and the row are equivalent. This
would suggest that I'd have to write code that does something like:

MVMap<...> map;
Integer group;

for (var entry : map.entrySet()) {
if (entry.getKey().group().equals(group)) {
// Collect member here
}
}

Which seems like it would be O(N) over the entire database just to
collect a subset of the "rows".

What's the expected/preferred way to do this so that I can efficiently
access members per-group?

--
Mark Raynsford | https://www.io7m.com

[Andreas Reichel]

On Sat, 2022-10-29 at 09:46 +0000, 'Mark Raynsford' via H2 Database wrote:

I'm slightly confused as to how to represent a particular data

structure efficiently. It's pretty simple, so I'll describe it in terms

of SQL:

  create table t (

    group integer not null,

    member integer not null,

    primary key (group, member)

  );

I think the natural way this would be expressed as a plain Java data

structure would be:

  Map<Integer, Set<Integer>>

Greetings,

based on your PRIMARY KEY it would be MAP<Key, T> with a Class Key ( int group, int member ) and a class T (int group, int member).

If group represents the key, and member represents the object, then PRIMARY KEY(group) and MAP<Integer, Integer>.

Best regards

Andreas

[Mark Raynsford]

On 2022-10-29T17:41:52 +0700

Hello!

I'm not sure about this. Wouldn't PRIMARY KEY(group) limit groups to a
single member?

Imagine I had the following rows:

(1, 1) // Group 1 contains member 1
(1, 23) // Group 1 also contains member 23
(1, 24) // Group 1 also contains member 24
(2, 4) // Group 2 contains member 4
(2, 5) // Group 2 contains member 5

With PRIMARY KEY(group), the above rows would violate the unique
constraint. With PRIMARY KEY(group, member), the above rows would
be accepted, and I'd be (correctly) prevented from saying that a member
is in a particular group multiple times.

[Andreas Reichel]

Well, it depends what you want.

But your MAP key should be corresponding with your Table PRIMARY KEY.

If you have a compound Primary Key, then your Map also must have a compound Key (not just an Integer) and you would likely need to build a Key Class implementing Comparable, Equals and Hash.

Cheers

Andreas

[Mark Raynsford]

On 2022-10-29T18:38:19 +0700

Andreas Reichel <and...@manticore-projects.com> wrote:
>
> Well, it depends what you want.
> But your MAP key should be corresponding with your Table PRIMARY KEY.
>
> If you have a compound Primary Key, then your Map also must have a
> compound Key (not just an Integer) and you would likely need to build a
> Key Class implementing Comparable, Equals and Hash.

I understand that, I think we may have gone down the wrong fork in the
road. :)

My question is about being able to declare one or more maps in a way
that will permit efficient (ie, not O(N)) to a subset of the keys in
a map.

Let's assume then that I have this as a composite primary key:

record GroupMember(int group, int member);

And I have a map:

MVMap<GroupMember, GroupMember> m;

This matches the definition of the `t` table I posted in the original
email; the primary key and the row are the same value.

How, then, do I efficiently ask for all rows that have a given group
value g?

I would have to do something like this:

var members = new ArrayList<Integer>();
for (var entry : m.entrySet()) {
if (entry.getKey().group() == g) {
members.add(entry.getKey().member());
}
}
return members;

This works, but is O(N) in the size of the map, whereas it _could_ be
O(1) (assuming that map access are O(1)) if I could declare the map
as being something analogous to:

MVMap<Integer, Set<Integer>> m;

I assume that I'm _not_ supposed to do this, given that MVStore doesn't
come with DataType definitions for any collection types.

Now obviously H2 is using MVStore internally for database tables, and
there it's obviously possible to have something like:

create table t (group integer not null, member integer not null);
create index t_groups on t (group);

... and then have "SELECT * FROM t where t.group = ?" be an indexed
scan instead of a seq scan over the entire t table... But what exactly
would an equivalent MVStore configuration look like?

[Ritu Jain]

Hi

I am using h2 database with mode as oracle ,  so need to define create alias for Oracle function to_number

On running the JUnit test case, the error comes : Invalid SQL script for the create alias statement.

Am I missing any dependency.

I have added dependency of H2 database. Am able to setup schema using create table also

Or can anyone suggest any alternative to functions like to_number and initcap that are Oracle support.

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/20221029132144.3b269aa0%40sunflower.int.arc7.info.