Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Sat, Nov 13, 2010 at 8:33 AM, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 11:59 PM, Keean Schupke ke...@fry-it.com wrote: Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Then we'd have to overload both the structured clone algorithm and the == javascript operator. Even with 53 bits you can generate a new ID a million times a second for 285 years. So I really don't think we need to worry. And if anyone is still worrying then I'd say lets look at this in a hundred years or so, at which point I suspect that javascript has grown support for 64bit integers bringing us back to that half a million year limit :) Any ID over 2^52ish is not useful though since you couldn't actually use it in JavaScript. We're simply saying that anything over 2^64 is disallowed to keep things simple. What's the difference with that and instead saying 2^52 is the limit? J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Wed, Nov 17, 2010 at 7:36 AM, Jeremy Orlow jor...@chromium.org wrote: On Sat, Nov 13, 2010 at 8:33 AM, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 11:59 PM, Keean Schupke ke...@fry-it.com wrote: Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Then we'd have to overload both the structured clone algorithm and the == javascript operator. Even with 53 bits you can generate a new ID a million times a second for 285 years. So I really don't think we need to worry. And if anyone is still worrying then I'd say lets look at this in a hundred years or so, at which point I suspect that javascript has grown support for 64bit integers bringing us back to that half a million year limit :) Any ID over 2^52ish is not useful though since you couldn't actually use it in JavaScript. We're simply saying that anything over 2^64 is disallowed to keep things simple. What's the difference with that and instead saying 2^52 is the limit? I agree. Sorry if I gave the impression of otherwise. (Note that this was not what Keean suggested in the email I reply to in your quote above). / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Cheers, Keean. On 12 November 2010 21:05, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 10:09 PM, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 12:36 AM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 11:27 AM, Keean Schupke ke...@fry-it.com wrote: You can do it in SQL because tables that hold a reference to an ID can declare the reference in the schema. I guess without the meta-data to do this it cannot be done. Even in SQL, I'd be very hesitant to do this. Why not get the auto-increment to wrap and skip collisions? What about signed numbers? Exactly. If we're going to support this, let's keep it super simple. As Jonas mentioned, it's very unlikely that anyone would hit the 64bit limit in legitimate usage, so it's not worth trying to gracefully handle such a situation and adding a lot of surface area. Indeed. I'd prefer to fail fatally to trying to do something complicated and clever here. I'd be surprised if anyone ever ran into this issue unintentionally (i.e. when not explicitly testing to see what happens). One way to look at it is that before we run into 2^64 limit, we'll run into the limit that javascript can't represent all integers above 2^53. So once IDs get above that you basically won't be able to use the object store anyway. Good point. Actually we probably need to spec the limit to be 2^52ish so that the auto number is never anything greater than what javascript can address. J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Fri, Nov 12, 2010 at 11:59 PM, Keean Schupke ke...@fry-it.com wrote: Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Then we'd have to overload both the structured clone algorithm and the == javascript operator. Even with 53 bits you can generate a new ID a million times a second for 285 years. So I really don't think we need to worry. And if anyone is still worrying then I'd say lets look at this in a hundred years or so, at which point I suspect that javascript has grown support for 64bit integers bringing us back to that half a million year limit :) / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
Hi, On 13 November 2010 08:33, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 11:59 PM, Keean Schupke ke...@fry-it.com wrote: Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Then we'd have to overload both the structured clone algorithm and the == javascript operator. Is that a problem? I can't see performance being an issue it has to determine which type of '==' to use anyway, and JavaScript does not appear to support Unboxing or Unboxed types. I accept that 2^53 bits is enough. To me though there is an advantage in not having the ID as an integer type. Basically the ID is an unordered sequence type. The only valid operators are '==' and '!='. ordered comparisons (greater, less) and maths mean nothing. I would think it better to use an opaque type so that people do not mistakenly think they can use these operators. It also allows implementers much more flexibility (and optimisation potential) in how they actually implement the IDs. Cheers, Keean.
Re: [Bug 11270] New: Interaction between in-line keys and key generators
Having said that, if its an opaque type, you could not supply values yourself, which is where this all started... and I think that is a good idea (for example when importing data). So whilst I think all the points I made in favour of an opaque type are true for this kind of thing in general, for this case I think the need to supply a value is more important. Personally I would like to see support for infinite-precision integers like Python has in JavaScript, but proper integer support would be a start. Cheers, Keean. On 13 November 2010 11:13, Keean Schupke ke...@fry-it.com wrote: Hi, On 13 November 2010 08:33, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 11:59 PM, Keean Schupke ke...@fry-it.com wrote: Why not return the full 64bit ID in an opaque object? Maths and comparing IDs is meaningless anyway. Then we'd have to overload both the structured clone algorithm and the == javascript operator. Is that a problem? I can't see performance being an issue it has to determine which type of '==' to use anyway, and JavaScript does not appear to support Unboxing or Unboxed types. I accept that 2^53 bits is enough. To me though there is an advantage in not having the ID as an integer type. Basically the ID is an unordered sequence type. The only valid operators are '==' and '!='. ordered comparisons (greater, less) and maths mean nothing. I would think it better to use an opaque type so that people do not mistakenly think they can use these operators. It also allows implementers much more flexibility (and optimisation potential) in how they actually implement the IDs. Cheers, Keean.
Re: [Bug 11270] New: Interaction between in-line keys and key generators
We can't compact because the developer may be expecting to look items up by ID with IDs in another table, on the server, in memory, etc. There's no way to do it. J On Fri, Nov 12, 2010 at 10:56 AM, Keean Schupke ke...@fry-it.com wrote: The other thing you could do is specify that when you get a wrap (IE someone inserts a key of MAXINT - 1) you auto-compact the table. If you really have run out of indexes there is not a lot you can do. The other thing to consider it that because JS uses signed arithmetic, its really a 63bit number... unless you want negative indexes appearing? (And how would that affect ordering and sorting)? Cheers, Keean. On 12 November 2010 07:36, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 10:08 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. This seems reasonable. OK, let's do it. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for this is restoring data into an _empty_ object store, right? (Because I don't think this is a good solution for much else.) That's the main scenario I can think of that would require this yes. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
You can do it in SQL because tables that hold a reference to an ID can declare the reference in the schema. I guess without the meta-data to do this it cannot be done. Why not get the auto-increment to wrap and skip collisions? What about signed numbers? Cheers, Keean. On 12 November 2010 08:23, Jeremy Orlow jor...@chromium.org wrote: We can't compact because the developer may be expecting to look items up by ID with IDs in another table, on the server, in memory, etc. There's no way to do it. J On Fri, Nov 12, 2010 at 10:56 AM, Keean Schupke ke...@fry-it.com wrote: The other thing you could do is specify that when you get a wrap (IE someone inserts a key of MAXINT - 1) you auto-compact the table. If you really have run out of indexes there is not a lot you can do. The other thing to consider it that because JS uses signed arithmetic, its really a 63bit number... unless you want negative indexes appearing? (And how would that affect ordering and sorting)? Cheers, Keean. On 12 November 2010 07:36, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 10:08 AM, Jonas Sicking jo...@sicking.ccwrote: On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. This seems reasonable. OK, let's do it. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for this is restoring data into an _empty_ object store, right? (Because I don't think this is a good solution for much else.) That's the main scenario I can think of that would require this yes. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Fri, Nov 12, 2010 at 11:27 AM, Keean Schupke ke...@fry-it.com wrote: You can do it in SQL because tables that hold a reference to an ID can declare the reference in the schema. I guess without the meta-data to do this it cannot be done. Even in SQL, I'd be very hesitant to do this. Why not get the auto-increment to wrap and skip collisions? What about signed numbers? Exactly. If we're going to support this, let's keep it super simple. As Jonas mentioned, it's very unlikely that anyone would hit the 64bit limit in legitimate usage, so it's not worth trying to gracefully handle such a situation and adding a lot of surface area. Cheers, Keean. On 12 November 2010 08:23, Jeremy Orlow jor...@chromium.org wrote: We can't compact because the developer may be expecting to look items up by ID with IDs in another table, on the server, in memory, etc. There's no way to do it. J On Fri, Nov 12, 2010 at 10:56 AM, Keean Schupke ke...@fry-it.com wrote: The other thing you could do is specify that when you get a wrap (IE someone inserts a key of MAXINT - 1) you auto-compact the table. If you really have run out of indexes there is not a lot you can do. The other thing to consider it that because JS uses signed arithmetic, its really a 63bit number... unless you want negative indexes appearing? (And how would that affect ordering and sorting)? Cheers, Keean. On 12 November 2010 07:36, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 10:08 AM, Jonas Sicking jo...@sicking.ccwrote: On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. This seems reasonable. OK, let's do it. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Fri, Nov 12, 2010 at 12:36 AM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 11:27 AM, Keean Schupke ke...@fry-it.com wrote: You can do it in SQL because tables that hold a reference to an ID can declare the reference in the schema. I guess without the meta-data to do this it cannot be done. Even in SQL, I'd be very hesitant to do this. Why not get the auto-increment to wrap and skip collisions? What about signed numbers? Exactly. If we're going to support this, let's keep it super simple. As Jonas mentioned, it's very unlikely that anyone would hit the 64bit limit in legitimate usage, so it's not worth trying to gracefully handle such a situation and adding a lot of surface area. Indeed. I'd prefer to fail fatally to trying to do something complicated and clever here. I'd be surprised if anyone ever ran into this issue unintentionally (i.e. when not explicitly testing to see what happens). One way to look at it is that before we run into 2^64 limit, we'll run into the limit that javascript can't represent all integers above 2^53. So once IDs get above that you basically won't be able to use the object store anyway. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Fri, Nov 12, 2010 at 10:09 PM, Jonas Sicking jo...@sicking.cc wrote: On Fri, Nov 12, 2010 at 12:36 AM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 11:27 AM, Keean Schupke ke...@fry-it.com wrote: You can do it in SQL because tables that hold a reference to an ID can declare the reference in the schema. I guess without the meta-data to do this it cannot be done. Even in SQL, I'd be very hesitant to do this. Why not get the auto-increment to wrap and skip collisions? What about signed numbers? Exactly. If we're going to support this, let's keep it super simple. As Jonas mentioned, it's very unlikely that anyone would hit the 64bit limit in legitimate usage, so it's not worth trying to gracefully handle such a situation and adding a lot of surface area. Indeed. I'd prefer to fail fatally to trying to do something complicated and clever here. I'd be surprised if anyone ever ran into this issue unintentionally (i.e. when not explicitly testing to see what happens). One way to look at it is that before we run into 2^64 limit, we'll run into the limit that javascript can't represent all integers above 2^53. So once IDs get above that you basically won't be able to use the object store anyway. Good point. Actually we probably need to spec the limit to be 2^52ish so that the auto number is never anything greater than what javascript can address. J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Thu, Nov 11, 2010 at 2:37 AM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 3:15 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 2:07 PM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto: public-webapps-requ...@w3.org] On Behalf Of bugzi...@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. This is how sqlite works too. It'd be great if we could make this required behavior. What would we do if what they provided was not an integer? What happens if the number they insert is so big that the next one causes overflow? What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
Integers can be big 8bytes is common. It is generally assumed that the auto-increment counter will be big enough, overflow would wrap, and if the ID already exists there would be an error. In my experience auto-increment columns must be integers. Cheers, Keean. On 11 November 2010 12:20, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 2:37 AM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 3:15 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 2:07 PM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto: public-webapps-requ...@w3.org] On Behalf Of bugzi...@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. This is how sqlite works too. It'd be great if we could make this required behavior. What would we do if what they provided was not an integer? What happens if the number they insert is so big that the next one causes overflow? What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.comwrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. J
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for this is restoring data into an _empty_ object store, right? (Because I don't think this is a good solution for much else.) That's the main scenario I can think of that would require this yes. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Fri, Nov 12, 2010 at 10:08 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. This seems reasonable. OK, let's do it. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for this is restoring data into an _empty_ object store, right? (Because I don't think this is a good solution for much else.) That's the main scenario I can think of that would require this yes. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
The other thing you could do is specify that when you get a wrap (IE someone inserts a key of MAXINT - 1) you auto-compact the table. If you really have run out of indexes there is not a lot you can do. The other thing to consider it that because JS uses signed arithmetic, its really a 63bit number... unless you want negative indexes appearing? (And how would that affect ordering and sorting)? Cheers, Keean. On 12 November 2010 07:36, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 10:08 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 9:22 PM, Jeremy Orlow jor...@chromium.org wrote: On Fri, Nov 12, 2010 at 12:32 AM, Jonas Sicking jo...@sicking.cc wrote: On Thu, Nov 11, 2010 at 11:41 AM, Jeremy Orlow jor...@chromium.org wrote: On Thu, Nov 11, 2010 at 6:41 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Thu, Nov 11, 2010 at 4:20 AM, Jeremy Orlow jor...@chromium.org wrote: What would we do if what they provided was not an integer? The behavior isn't very important; throwing would be fine here. In mySQL, you can only put AUTO_INCREMENT on columns in the integer family. What happens if the number they insert is so big that the next one causes overflow? The same thing that happens if you do ++ on a variable holding a number that's too large. Or, more directly, the same thing that happens if you somehow fill up a table to the integer limit (probably deleting rows along the way to free up space), and then try to add a new row. What is the use case for this? Do we really think that most of the time users do this it'll be intentional and not just a mistake? A big one is importing some data into a live table. Many smaller ones are related to implicit data constraints that exist in the application but aren't directly expressed in the table. I've had several times when I could normally just rely on auto-numbering for something, but occasionally, due to other data I was inserting elsewhere, had to specify a particular id. This assumes that your autonumbers aren't going to overlap and is going to behave really badly when they do. Honestly, I don't care too much about this, but I'm skeptical we're doing the right thing here. Pablo did bring up a good use case, which is wanting to migrate existing data to a new object store, for example with a new schema. And every database examined so far has some ability to specify autonumbered columns. overlaps aren't a problem in practice since 64bit integers are really really big. So unless someone maliciously sets a number close to the upper bound of that then overlaps won't be a problem. Yes, but we'd need to spec this, implement it, and test it because someone will try to do this maliciously. I'd say it's fine to treat the range of IDs as a hardware limitation. I.e. similarly to how we don't specify how much data a webpage is allowed to put into DOMStrings, at some point every implementation is going to run out of memory and effectively limit it. In practice this isn't a problem since the limit is high enough. Another would be to define that the ID is 64 bit and if you run out of IDs no more rows can be inserted into the objectStore. At that point the page is responsible for creating a new object store and compacting down IDs. In practice no page will run into this limitation if they use IDs increasing by one. Even if you generate a new ID a million times a second, it'll still take you over half a million years to run out of 64bit IDs. This seems reasonable. OK, let's do it. And, in the email you replied right under, I brought up the point that this feature won't help someone who's trying to import data into a table that already has data in it because some of it might clash. So, just to make sure we're all on the same page, the use case for this is restoring data into an _empty_ object store, right? (Because I don't think this is a good solution for much else.) That's the main scenario I can think of that would require this yes. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@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. What happens if the property is missing several parents, such as foo.bar.baz { zip: {} } Does this throw or does it store { zip: {}, foo: { bar: { baz: 4 } } } We should just complete the object with all the missing parents. Agreed. If we end up allowing array indexes in key paths (like foo[1].bar) what does the following keypath/object result in? I think we can live without array indexing in keys for this round, it's probably best to just leave them out and only allow paths. Agreed. ~TJ
RE: [Bug 11270] New: Interaction between in-line keys and key generators
From: Tab Atkins Jr. [mailto:jackalm...@gmail.com] Sent: Wednesday, November 10, 2010 1:50 PM On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@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. That would be okay with me. One bit of fine-print on this one is that if you're calling store.add() with an explicit key then you may get a unique constraint error (which would never happen with a generator if you never provided your own keys). Also, did we settle for having put() never adding a new record if one didn't exist? If put() can create a record, then things still work but become a bit more elaborate in that put() would create a new record either if the key is not present in the object or if it's present but the value doesn't exist in the database, while it would update a record if the value was present and it existed as a key in the store. -pablo
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@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? What happens if the property is missing several parents, such as foo.bar.baz { zip: {} } Does this throw or does it store { zip: {}, foo: { bar: { baz: 4 } } } We should just complete the object with all the missing parents. Agreed. Works for me. If we end up allowing array indexes in key paths (like foo[1].bar) what does the following keypath/object result in? I think we can live without array indexing in keys for this round, it's probably best to just leave them out and only allow paths. Agreed. Works for me. Actually, we could go even further and disallow paths entirely, and just allow a property name. That is what the firefox implementation currently does. That also sidesteps the issue of missing parents. / Jonas
Re: [Bug 11270] New: Interaction between in-line keys and key generators
Actually, we could go even further and disallow paths entirely, and just allow a property name. That is what the firefox implementation currently does. That also sidesteps the issue of missing parents. I'm not convinced that people are going to bury their key several levels deep on the documents they store. +1 for this. -Ben
Re: [Bug 11270] New: Interaction between in-line keys and key generators
What do databases usually do with columns that use autoincrement but a value is still supplied? My recollection is that that is generally allowed? You can normally insert with a supplied key providing it is unique. Cheers, Keean. On 10 November 2010 22:07, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto: public-webapps-requ...@w3.org] On Behalf Of bugzi...@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? What happens if the property is missing several parents, such as foo.bar.baz { zip: {} } Does this throw or does it store { zip: {}, foo: { bar: { baz: 4 } } } We should just complete the object with all the missing parents. Agreed. Works for me. If we end up allowing array indexes in key paths (like foo[1].bar) what does the following keypath/object result in? I think we can live without array indexing in keys for this round, it's probably best to just leave them out and only allow paths. Agreed. Works for me. Actually, we could go even further and disallow paths entirely, and just allow a property name. That is what the firefox implementation currently does. That also sidesteps the issue of missing parents. / Jonas
RE: [Bug 11270] New: Interaction between in-line keys and key generators
From: Jonas Sicking [mailto:jo...@sicking.cc] Sent: Wednesday, November 10, 2010 2:08 PM On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@jessica.w3.org Sent: Monday, November 08, 2010 5:07 PM 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? It does happen in practice that sometimes you need to use explicit keys. The typical case is when you're initializing a database with base data and you want to have known keys. As for what databases do, I'll use SQL Server as an example (for no particular reason :) ). In SQL Server by default if you try to insert a row with a value in an identity column you get an error and the operation is aborted; however, developers can issue a command (SET IDENTITY_INSERT table ON) to turn it off temporarily and insert rows with an explicitly provided primary key. Usually when you do this you have to be careful to use keys that are either way out of the range of keys the generator will use (or you may not be able to insert keys anymore) or you have to reset the next key (using an obscure DBCC CHECKIDENT (table, RESEED, next-key) command). I don't know much about Oracle, but I believe the typical pattern is still to use a sequence object and set the default value for the key column to sequence.nextval, thus allowing callers to override the next value in the sequence by just providing one, and if necessary they may need to go and fix up the sequence. From writing the above paragraph I'm realizing one more detail we need to be explicit about: the fact that you do an add() with an explicit key does not mean the implementation will fix up the next key it'll assign. You'll still get the value that comes after the one generated last, and if you inserted that value in the store explicitly you just made the store unable to add new objects with generated keys until you delete it. If that's too much fine-print then we should just disallow it. I like the ability to set explicit key values, but it does come with some extra care that both implementers and users will have to have. -pablo
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Wed, Nov 10, 2010 at 2:07 PM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@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
Re: [Bug 11270] New: Interaction between in-line keys and key generators
On Wed, Nov 10, 2010 at 3:15 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 2:07 PM, Jonas Sicking jo...@sicking.cc wrote: On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. jackalm...@gmail.com wrote: On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro pablo.cas...@microsoft.com wrote: From: public-webapps-requ...@w3.org [mailto:public-webapps-requ...@w3.org] On Behalf Of bugzi...@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. This is how sqlite works too. It'd be great if we could make this required behavior. / Jonas
[Bug 11270] New: Interaction between in-line keys and key generators
http://www.w3.org/Bugs/Public/show_bug.cgi?id=11270 Summary: Interaction between in-line keys and key generators Product: WebAppsWG Version: unspecified Platform: PC OS/Version: All Status: NEW Severity: normal Priority: P2 Component: Indexed Database API AssignedTo: dave.n...@w3.org ReportedBy: jo...@sicking.cc QAContact: member-webapi-...@w3.org CC: m...@w3.org, public-webapps@w3.org This is related to, and affected by, bug 9832 and bug 11269. When adding a value to an object store which uses in-line keys and key generators keypaths are used to point out where a value should be *modified*. A key is generated and written to the stored value, and the location in the stored value is determined using the object store's keypath. 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 } }? What happens if the property is missing several parents, such as foo.bar.baz { zip: {} } Does this throw or does it store { zip: {}, foo: { bar: { baz: 4 } } } If we end up allowing array indexes in key paths (like foo[1].bar) what does the following keypath/object result in? foo[0] { foo: [10] } foo[1] { foo: [10] } foo[5] { foo: [10] } -- Configure bugmail: http://www.w3.org/Bugs/Public/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug.