Database-wide unique primary keys

[Ross Catrow]

I'm totally new to both persistence and sqlite, so forgive me, etc
etc.

I was wondering what is the reasoning behind forcing rows to have
database-wide unique primary keys? You can see this in
persistence.add() around line 304. This is kind of a huge bummer for
me as I pull a json dump from a MySQL database. So, I've got tons of
primary keys that are unique to their table but not database-wide.

My, inelegant, solution is to set the primary keys for table A to
start at 100, table B to start at 1000, and so on. Which kind of
sucks.

Anyway, thanks for your time! Keep up the good work.

[Zef Hemel]

Hi Ross,

Early on the design decision was made to assign random UUIDs to every
object. There were two reasons for this:

1. A main use case of perisistence.js is to cache and sync data with a
remote server. Safely assigning unique incremental IDs in a
distributed system like that is difficult, if not impossible. For
instance, new sequential IDs may be assigned to new rows in the
browser's database that are also used in the remote database. Use of
UUIDs circumvent that problem.

2. It greatly simplifies the implementation because an object ID can
be assigned by the persistency library when the object is created
rather than being assigned by the database when it is first persisted.

The drawback is that a UUID uses more space in the local database.
It's a trade-off.

You can can still store table-specific IDs in your entities, of
course. You can simply add an additional id column to your entities.
These identifiers can be anything you want and be table-specific,
coming from MySQL. Still, in addition, every object will still have an
automatically assigned UUID. If that is not a problem for you,
everything should work just fine.

Hope that helps.

Best,

Zef

--
Zef Hemel
http://zef.me
http://twitter.com/zef

[Ross Catrow]

Yeah, that was my original idea for a solution. But then I started
playing around with relationships between tables. It looks like
persistence automatically uses a field called "id" to look up, for
example, a hasOne relationship? So if it is assigning a random UUID to
a field called id, even if I have a second field called, say,
artist_id, it wouldn't use that field for joins. Is that correct?

Is there a way to choose what field you'd like to use to join tables?

I do understand the design decision though. Perhaps my use of
persistence isn't the main usage case.

[Zef Hemel]

Hi Ross,

I'm not sure how you import your data in the browser's database, but
you could just recreate the relationships on the objects manually, I
suppose?

var obj1 = new Something({name: jsonObj.name, ...});
var obj2 = new SomethingElse({name: otherJsonObj.name, ...});
obj.someRel = obj2; // create N:1 or 1:1 relationship
// or
obj.someColl.add(obj); // create 1:N or N:M relationship

Or are are you dumping your objects straight into the local DB using
SQL queries? That would make it more difficult.

The foreign key columns are not configurable, no, sorry.

HTH,

Zef

--