Re: [Bug 11270] New: Interaction between in-line keys and key generators

On Wed, Nov 10, 2010 at 2:07 PM, Jonas Sicking <jonas@sicking.cc> wrote:
> On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. <jackalmage@gmail.com> wrote:
>> On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro
>> <Pablo.Castro@microsoft.com> wrote:
>>>
>>> From: public-webapps-request@w3.org [mailto:public-webapps-request@w3.org] On Behalf Of bugzilla@jessica.w3.org
>>> Sent: Monday, November 08, 2010 5:07 PM
>>>
>>>>> So what happens if trying save in an object store which has the following
>>>>> keypath, the following value. (The generated key is 4):
>>>>>
>>>>> "foo.bar"
>>>>> { foo: {} }
>>>>>
>>>>> Here the resulting object is clearly { foo: { bar: 4 } }
>>>>>
>>>>> But what about
>>>>>
>>>>> "foo.bar"
>>>>> { foo: { bar: 10 } }
>>>>>
>>>>> Does this use the value 10 rather than generate a new key, does it throw an
>>>>> exception or does it store the value { foo: { bar: 4 } }?
>>>
>>> I suspect that all options are somewhat arbitrary here. I'll just propose that we error out to ensure that nobody has the wrong expectations about the implementation preserving the initial value. I would be open to other options except silently overwriting the initial value with a generated one, as that's likely to confuse folks.
>>
>> It's relatively common for me to need to supply a manual value for an
>> id field that's automatically generated when working with databases,
>> and I don't see any particular reason that my situation would change
>> if using IndexedDB.  So I think that a manually-supplied key should be
>> kept.
>
> I'm fine with either solution here. My database experience is too weak
> to have strong opinions on this matter.
>
> What do databases usually do with columns that use autoincrement but a
> value is still supplied? My recollection is that that is generally
> allowed?

I can only speak from my experience with mySQL, which is generally
very permissive, but which has very sensible behavior here imo.

You are allowed to insert values manually into an AUTO_INCREMENT
column.  The supplied value is stored as normal.  If the value was
larger than the current autoincrement value, the value is increased so
that the next auto-numbered row will have an id one higher than the
row you just inserted.

That is, given the following inserts:

insert row(val) values (1);
insert row(id,val) values (5,2);
insert row(val) values (3);

The table will contain [{id:1, val:1}, {id:5, val:2}, {id:6, val:3}].

If you have uniqueness constraints on the field, of course, those are
also used.  Basically, AUTO_INCREMENT just alters your INSERT before
it hits the db if there's a missing value; otherwise the query is
treated exactly as normal.

~TJ

Received on Wednesday, 10 November 2010 23:16:40 UTC