Re: [h2] How are "multiple rows" represented in MVStore?
On 2022-10-29T18:38:19 +0700
Andreas Reichel 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 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();
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> 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?
--
Mark Raynsford | https://www.io7m.com
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/20221029132144.3b269aa0%40sunflower.int.arc7.info.
Re: [h2] How are "multiple rows" represented in MVStore?
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 On Sat, 2022-10-29 at 10:50 +, 'Mark Raynsford' via H2 Database wrote: > > > 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 -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3b3a93f4cf6dc756c95928be661b7d2e1651437d.camel%40manticore-projects.com.
Re: [h2] How are "multiple rows" represented in MVStore?
On 2022-10-29T17:41:52 +0700 Andreas Reichel wrote: > On Sat, 2022-10-29 at 09:46 +, 'Mark Raynsford' via H2 Database > wrote: > > I think the natural way this would be expressed as a plain Java data > > structure would be: > > > > Map> > > Greetings, > > based on your PRIMARY KEY it would be MAP 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. 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. -- Mark Raynsford | https://www.io7m.com -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/20221029105032.3f77e8f2%40sunflower.int.arc7.info.
Re: [h2] How are "multiple rows" represented in MVStore?
On Sat, 2022-10-29 at 09:46 +, '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> Greetings, based on your PRIMARY KEY it would be MAP 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. Best regards Andreas -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0f147504216fdd15e90ded31cabaad56a98cecce.camel%40manticore-projects.com.
[h2] How are "multiple rows" represented in MVStore?
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>
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
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/20221029094638.61cd60e9%40sunflower.int.arc7.info.
