WebSQL - many-to-many

[cboulay]

How to handle objects that do not have a primary id. e.g., teamplayer relies on its team id and player id, but does not have an id itself. I can't think of a generic solution to this. For now teamplayer and similar objects without an id use the default sync'ing (i.e., not websql). After solving the below problems I might decide to make a custom db that mirrors the exposed API resources then I can use more complicated SQL (triggers and stored procedures) to manage this type of model.

[mliu7]

Hmm, if this is going to be a difficult issue for you, this will likely be a difficult issue for other developers as well. I am willing to consider our API design to make PUT, GET detail, and DELETE requests on these many-to-many resources use a primary ID instead of 2 unique IDs. Do you think you could elaborate on this issue a bit more?

[cboulay]

Right now the plugin is designed to be generic. It should work with any standard backbone class, provided the model of that class has an id. If I wanted the WebSQL plugin to work with classes that did not have a single id then I would have to write the SQL code for each model type manually. In fact I'm thinking of doing this anyway.

One of the disadvantages of the generic approach is that I don't have individual columns for individual attributes, meaning I have to SELECT all and then filter what's returned or filter what's displayed. If I write out the SQL code independently for each class then I can SELECT WHERE to return smaller result sets which should be faster.

In short: yes, using a primary ID would help me to keep things simple and get this running in the short-term; however, I may not use it in the long-term.

[mliu7]

Ok, thanks for the info. I am pretty convinced now that using a single ID will be better.

The challenge now, is that the API is already live and we really don't want to break any existing apps. If we made this change, would it break the existing Ultistats?

I can ask the other Leaguevine developers as well.

[mliu7]

Does the primary ID have to be an integer? If it doesn't, the ID can be the concatenation of the player and team IDs (i.e. "312/4639").

I talked to a guy running a REST API conference called REST Fest, and that's what he recommended. He said that keeping the URI structure with both the Player and Team IDs in it is probably the most appropriate and RESTful way to handle things.

[cboulay]

Right now the WebSQL plugin requires that the ids be integers but there is no good reason for that. In fact, Backbone treats the ids as strings so I should do the same in the local db.

[mliu7]

Ok, awesome. I'm glad to hear that about Backbone. Thanks for checking into that. And I hope it's not too much work to convert the WebSQL plugin to using strings.

[cboulay]

Just a note to self: The database is working better. However, I haven't been able to get any many-to-many stored in the database yet.