Index problem when using Object.object entity

[almozavr]

When there is:

@entity
public class Message extends Model{

    @PrimaryKey(autoIncrement = true)
    public int rowId;
    @Index
    public int id;
    public int title;
    public int text;
    @OneToOne
    public Message parentMessage;
}

error occurs saying column parent_message is not unique

It's a some kind of restriction, but! One should feel free to use unique indexes even in this situation.
P.S. everything works if I use set @Index as not unique.

[ahmetb]

Can you give exact exception stack trace instead of just the error message, please? Thanks.

On Sep 16, 2011, at 3:54 PM, Aleksey Malevaniy wrote:

When there is:

@entity
public class Message extends Model{

   @PrimaryKey(autoIncrement = true)
   public int rowId;
   @Index
   public int id;
   public int title;
   public int text;
   @OneToOne
   public Message parentMessage;
}

error occurs saying column parent_message is not unique

It's a some kind of restriction, but! One should feel free to use unique indexes even in this situation.
P.S. everything works if I use set @Index as not unique.

Reply to this email directly or view it on GitHub:
#33

[almozavr]

Surely, sorry. Here it is:

Caused by: org.orman.dbms.exception.QueryExecutionException: Query execution error: SQLiteAndroid error:android.database.sqlite.SQLiteConstraintException: column parent_message is not unique: INSERT INTO ...

[ahmetb]

Now you can see that it is not an Orman constraint but Android SQLite implementation constraint. We also were not aware of that. If you can share your app logic with us (which column is referenced by parent_message e.g.) maybe we can help.

But in most cases, parent side of foreign keys has to be unique fields since they directly point a single entry and duplicate values on that field may cause confusion. This is not related to the software that you are creating, it is about relational database theory.

[almozavr]

Oh, now I see, it's really obviously, thanks. Sorry for bothering.

BTW, some words about my app logic and orman experience: as I'm writing some kind of messenger, I have to use wrapper methods for insert/update objects to keep consistency. So I have some ideas how framework could help with this work even in complicated situation. I'll try to share app logic and code this weekends ;-)

[almozavr]

One more thing.

In my app I definitely have to live without UNIQUE index when creating @OnetoOne relation. In example above you can see that Message has a reference to parent Message (it creates cascade, 'cause one message could be a comment to another). And some messages has null as a parent (here comes unique violation).

Is there is a way to create NOT unique index for @OnetoOne? Or how you recommend to deal with this situation?

[ahmetb]

You can just store an integer in that field and just say that private int parent_message in your entity class, without any annotations.

Then you can use ModelQuery to query object with that id. I think we haven't undocumented ModelQuery yet.

I will think that you have an @entity called Message and it has an int field id. It should be:

Message parentMessage = Model.fetchSingle(
                            ModelQuery.select().from(Message.class)
                                .where(C.eq(Message.class, "id", 99)).getQuery(),
                            Message.class);

Here you should note that

• "id" is field name as appears in @entity Java class
• You should replace value of 99 with yourInstance.getParentMessage() (which returns id that you store)

This query returns null if not found. With that solution what you do is to actually storing just integer value of the parent message id in a field and then querying it manually. If you wouldn't have that constraint, you would be using @OnetoOne and your getParentMessage() will be returning Message instance whose id is stored in that field automatically.

[almozavr]

Thank again!

I understand this approach but I tried to avoid it, because everybody wants to work with objects without any hacks :) I also tried to set unique=false but as you wrote earlier it causes sql db to break down...

Now I think it would be interesting to find out how other ORMs deal with it. Maybe, there is a solution... I'll try to investigate.

[ahmetb]

I think none of the relational DBMSes on the earth would allow you to create FK to non-unique indexed fields. Therefore none of the ORMs will be handling it specially. Assume that I say that my parent is 99. But in the database id:99 corresponds to two different values. Who is my parent now?

If you are going to have multiple parents, then you should use @OneToMany on parent field, that verbally means I (one) have (many) parents.

[almozavr]

Your're right and my weak knowledge of RDBMS.
The approach with storing only int value and fetching object by it
works well enough.

Regards,
Aleksey Malevaniy

On 16 September 2011 17:23, ahmet alp balkan
reply@reply.github.com
wrote:

I think none of the relational DBMSes on the earth would allow you to create FK to non-unique indexed fields. Therefore none of the ORMs will be handling it specially. Assume that I say that my parent is 99. But in the database id:99 corresponds to two different values. Who is my parent now?

If you are going to have multiple parents, then you should use @OneToMany on parent field, that verbally means I (one) have (many) parents.

Reply to this email directly or view it on GitHub:
#33 (comment)