GAE datastore foreign key and join

Question

i'm struggling with gae for something that looks very simple to me. let me explain. i have a table with data about a person, i decided to use email as id

@Entity
public class Person{
@Id
@Column(name = "email")
String email;
...

what i would like to accomplish is

1. create a table with two columns, both containing email from Person, with the meaning "email in column 1 has written to email in column 2"
2. when i delete a row from Person, i would like that all the rows in the table at point 1 that contain this Person email in column 1 or 2 would be deleted as a cascade effect
3. i want to query my database so that, given an email address, i will be able to join all the rows in the table at point 1 and extract all the datas (name, phone...) of the Persons the given email has written to.

trouble is that apparently in gae i cannot use join, and i simply can't understand how to create a join table with cascade effect. any help is welcome. thanks in advance

Answer 1

Datastore isn't a relational database, so you should familiarise yourself with the concepts before starting to design a solution. What you're trying to do is fit a square peg into a round hole: not only will you find you're missing joins, you will also have to implement your own cascade-on-delete (hint: you may not want to do this but if you did, and you have a lot of data, look at Task Queues).

You don't provide much in the way of code, and I don't know JPA (tip: look at Objectify, it's much more suitable for the non-relational Datastore) but you might want something like this (using Objectify annotations):

@Entity
public class Person {
    @Id
    String email;
    ...
}

Then I'm assuming you will have some kind of Message entity (what you refer to as a two-column table):

@Entity
public class Message {
    @Id
    Long msgId;
    @Index
    Ref<Person> from;
    @Index
    Ref<Person> to;
    ...
}

Depending on what queries you need to perform, you may need to create a custom index (read here). Remember, queries on Datastore are index scans.

But, say, you want to get messages sent from Person A to Person B, you can do something like:

Person a = ofy().load().type(Person.class).id("[email protected]").now();
Person b = ofy().load().type(Person.class).id("[email protected]").now();
...
ofy().load().type(Person.class).filter("from =", Ref.create(a)).filter("to =", Ref.create(b)).list();

Instead of using Ref<Person> (essentially a Key), you could of course use a String representing the email. You may also not want to use email as the @Id as that would prevent a user changing their email address.