Re: [whatwg] Database feedback
On Wed, 26 Nov 2008, Jim Jewett wrote: I would name the question mark character aqt least once, so that it can't be mistaken for a placeholder in the spec (rather than in the SQL): Where it now says: Parse the first argument to the method (sqlStatement) as a SQL statement, with the exception that ? characters can be used in place of SQL literals in the statement. [SQL] Done. This still leaves open whether the statement has to actually be PREPAREd, and whether other parameter types or host variables are supported. I'm not sure what you mean. There are systems where you need to prepare a SQL statement before you can execute it, but I think the current wording makes clear that this system works from the strings, and does the PREPARE-ation itself as part of execution. Yeah, that's an implementation detail. On Wed, 26 Nov 2008, Aaron Boodman wrote: On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson i...@hixie.ch wrote: We could have a .writeTransaction() and a .readTransaction(), where the former always run in isolation. Any preferences? My preference is for separating read transactions from write transactions. Then the API could throw if you tried to write in a read transaction. On Wed, 3 Dec 2008, Scott Hess wrote: In SQLite the lock is upgraded as-needed, and it does cause confusion. The following can fail to get the write lock at the marked statement: BEGIN DEFERRED; SELECT x, y, z FROM t WHERE q = ?; INSERT INTO j VALUES (?, ?, ?); -- *mark* COMMIT; In SQLite BEGIN is the same as BEGIN DEFERRED. Gears changes the default for BEGIN to BEGIN IMMEDIATE, which acquires the lock right away. If a developer wants to do a read-only transaction, they can explicitly execute BEGIN DEFERRED. In any case, my point is that upgrading mid-stream seems to be a point of confusion for people. You can explain what's happening, and they will understand it, but the case comes up seldom enough that most people never internalize it. Requiring an explicit decision up front makes things clear, and doesn't absurdly widen the API. I would lean towards .transaction() and .readTransaction(), though. Acquiring the lock immediately is almost always the right thing to do. You can code a join to get consistent data from multiple tables, but you cannot code a single statement to insert data consistently into multiple tables (ignoring triggers), which is why read-only explicit transactions are rare, and write-only explicit transactions are common. I'd say that write-mostly transactions (one or two reads followed by a bunch of writes) are probably also more common than read-only transactions. On Wed, 26 Nov 2008, Jonas Sicking wrote: I'm fine with either the perf-hit solution or the .writeTransaction/.readTransaction solution. Both seems better than having writes throw under basically race conditions. If we go with the perf-hit solution we can always add the speedier .writeTransaction/.readTransaction APIs in a later version. I've introduced transaction()/readTransaction(), and made readTransaction() fail when executeSql() is called with a method that would mutate the database. On Wed, 26 Nov 2008, Chris Prince wrote: On Mon, 26 May 2008, Chris Prince wrote: // On the 1st call, this line means create a database, // and set the version string to the empty string. var db1 = window.openDatabase(foo, , , ); // On the 2nd call, the meaning has changed to // open the 'foo' database, regardless of the version string. var db2 = window.openDatabase(foo, , , ); On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson i...@hixie.ch wrote: Yeah, that's a bit confusing. Not sure what to do about it. Two ideas: * Perhaps use a different value to mean open any version, if one exists. Using 'null' seems like an obvious candidate. * Alternately, don't allow the empty string as a valid version string. Only use the empty string to mean open existing. Well the thing is that if the database doesn't exist, I still want the open the database regardless of its version case to actually open (and create) the database. The problem is what to use as the version string in that case. -- Ian Hickson U+1047E)\._.,--,'``.fL http://ln.hixie.ch/ U+263A/, _.. \ _\ ;`._ ,. Things that are impossible just take longer. `._.-(,_..'--(,_..'`-.;.'
Re: [whatwg] Database feedback
On Wed, Nov 26, 2008 at 8:58 AM, Aaron Boodman [EMAIL PROTECTED] wrote: On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson [EMAIL PROTECTED] wrote: We could have a .writeTransaction() and a .readTransaction(), where the former always run in isolation. Any preferences? My preference is for separating read transactions from write transactions. Then the API could throw if you tried to write in a read transaction. In SQLite the lock is upgraded as-needed, and it does cause confusion. The following can fail to get the write lock at the marked statement: BEGIN DEFERRED; SELECT x, y, z FROM t WHERE q = ?; INSERT INTO j VALUES (?, ?, ?); -- *mark* COMMIT; In SQLite BEGIN is the same as BEGIN DEFERRED. Gears changes the default for BEGIN to BEGIN IMMEDIATE, which acquires the lock right away. If a developer wants to do a read-only transaction, they can explicitly execute BEGIN DEFERRED. In any case, my point is that upgrading mid-stream seems to be a point of confusion for people. You can explain what's happening, and they will understand it, but the case comes up seldom enough that most people never internalize it. Requiring an explicit decision up front makes things clear, and doesn't absurdly widen the API. I would lean towards .transaction() and .readTransaction(), though. Acquiring the lock immediately is almost always the right thing to do. You can code a join to get consistent data from multiple tables, but you cannot code a single statement to insert data consistently into multiple tables (ignoring triggers), which is why read-only explicit transactions are rare, and write-only explicit transactions are common. I'd say that write-mostly transactions (one or two reads followed by a bunch of writes) are probably also more common than read-only transactions. -scott
[whatwg] Database feedback
There's a question at the bottom about how best to make transactions be free of concurrency problems. Input welcome. On Fri, 23 May 2008, Aaron Boodman wrote: I noticed one unfortunate thing about the new Database API. Because the executeSql() callback holds open the transaction, it is easy to accidentally do intensive work inside there and hold open the transaction too long. A common mistake might be to do a big select and then hold open the transaction while the UI is updated. This could hold open the tx maybe an extra couple hundred ms. A bigger problem would be to do synchronous XHR (for example, in workers). This could hold open the tx for seconds. The right place to do work like this is in transaction()'s success callback. But because the resultsets aren't easily accessible there, I think authors are more likely to do work in executeSql()'s success callback and use transaction()'s success callback less frequently. Off hand about the best solution I can think of to this problem is to have some sort of state on the transaction object that gathers the results. This is not very satisfying though. Does anyone have a better idea? Or think this is not a big enough concern to worry about? I agree that this might be a problem. I'm not sure how to address it. On Tue, 27 May 2008, Scott Hess wrote: I think the only way you can really make it better is to put something like an executeSql() function directly on Database objects, which could shift the success callback entirely outside the transaction scope. I think that would reasonably match server-side SQL use (you either send a bare SELECT with implicit transaction, or use explicit BEGIN/END to group things which must be consistent). I don't want to add this yet, in case it turns out we don't really need it, but implementation experience will definitely tell us whether this is needed or not and if it is we will have to add it in v2. On Mon, 26 May 2008, Aaron Boodman wrote: Quick thing. I don't think the spec makes it clear whether it is allowed to do this: var db1 = window.openDatabase(foo, , , ); var db2 = window.openDatabase(foo, 1, , ); I think the answer should be no. Thoughts? If so, perhaps edit this sentence: If the database version provided is not the empty string, and the database already exists but has a different version, then the method must raise an INVALID_STATE_ERR exception. To read like this: If the database version provided is not the empty string, and the database already exists but has a different version, or no version, then the method must raise an INVALID_STATE_ERR exception. Fixed. On Mon, 26 May 2008, Chris Prince wrote: I think the spec is technically correct. What's confusing is that the same line can mean two different things: // Start with no databases. // On the 1st call, this line means create a database, // and set the version string to the empty string. var db1 = window.openDatabase(foo, , , ); // On the 2nd call, the meaning has changed to // open the 'foo' database, regardless of the version string. var db2 = window.openDatabase(foo, , , ); Yeah, that's a bit confusing. Not sure what to do about it. On Mon, 4 Aug 2008, Aaron Boodman wrote: It seems like you need a way to abort an in-progress transaction. An easy way to do this would be to add an abort() method to SQLTransaction. What's the use case? Can we wait until v2 to add this, or is it critical? On Mon, 4 Aug 2008, Aaron Boodman wrote: Currently, the database API has an error code for the situation where you open a transaction for read, then try to write but the database is locked. I think that the spec should at least suggest, but perhaps require, implementors to serialize access to a single database to prevent this from happening. Without this, developers must wrap every single write attempt in error handling and retry if a lock error occurs. It seems likely that developers will forget to do this and it will be a significant pain point with the API. Applications will seem to work, but then mysteriously fail in production when users have multiple copies of the application open. Even if the developer adds retry logic, it is easy for a page to get starved by another page. Serializing access would prevent all these problems at the cost of read concurrency, which I think is OK for the first version of this API. A future version of the API could add the concept of read transactions which would allow concurrency, but would fail immediately when you try to write with them. I am loath to do this, because of the performance hit. However, what we could do is say that if the first statement is read-only, they all have to be, and if the first statement is a write, then it locks the database? Though that would be rather weird... We could have a .writeTransaction() and a .readTransaction(), where the former always
Re: [whatwg] Database feedback
On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson [EMAIL PROTECTED] wrote: We could have a .writeTransaction() and a .readTransaction(), where the former always run in isolation. Any preferences? My preference is for separating read transactions from write transactions. Then the API could throw if you tried to write in a read transaction. - a
Re: [whatwg] Database feedback
On Mon, 26 May 2008, Chris Prince wrote: // On the 1st call, this line means create a database, // and set the version string to the empty string. var db1 = window.openDatabase(foo, , , ); // On the 2nd call, the meaning has changed to // open the 'foo' database, regardless of the version string. var db2 = window.openDatabase(foo, , , ); On Wed, Nov 26, 2008 at 3:46 AM, Ian Hickson [EMAIL PROTECTED] wrote: Yeah, that's a bit confusing. Not sure what to do about it. Two ideas: * Perhaps use a different value to mean open any version, if one exists. Using 'null' seems like an obvious candidate. * Alternately, don't allow the empty string as a valid version string. Only use the empty string to mean open existing. --Chris
Re: [whatwg] Database feedback
Ian Hickson wrote: There's a question at the bottom about how best to make transactions be free of concurrency problems. Input welcome. On Fri, 23 May 2008, Aaron Boodman wrote: I noticed one unfortunate thing about the new Database API. Because the executeSql() callback holds open the transaction, it is easy to accidentally do intensive work inside there and hold open the transaction too long. A common mistake might be to do a big select and then hold open the transaction while the UI is updated. This could hold open the tx maybe an extra couple hundred ms. A bigger problem would be to do synchronous XHR (for example, in workers). This could hold open the tx for seconds. The right place to do work like this is in transaction()'s success callback. But because the resultsets aren't easily accessible there, I think authors are more likely to do work in executeSql()'s success callback and use transaction()'s success callback less frequently. Off hand about the best solution I can think of to this problem is to have some sort of state on the transaction object that gathers the results. This is not very satisfying though. Does anyone have a better idea? Or think this is not a big enough concern to worry about? I agree that this might be a problem. I'm not sure how to address it. On Tue, 27 May 2008, Scott Hess wrote: I think the only way you can really make it better is to put something like an executeSql() function directly on Database objects, which could shift the success callback entirely outside the transaction scope. I think that would reasonably match server-side SQL use (you either send a bare SELECT with implicit transaction, or use explicit BEGIN/END to group things which must be consistent). I don't want to add this yet, in case it turns out we don't really need it, but implementation experience will definitely tell us whether this is needed or not and if it is we will have to add it in v2. On Mon, 26 May 2008, Aaron Boodman wrote: Quick thing. I don't think the spec makes it clear whether it is allowed to do this: var db1 = window.openDatabase(foo, , , ); var db2 = window.openDatabase(foo, 1, , ); I think the answer should be no. Thoughts? If so, perhaps edit this sentence: If the database version provided is not the empty string, and the database already exists but has a different version, then the method must raise an INVALID_STATE_ERR exception. To read like this: If the database version provided is not the empty string, and the database already exists but has a different version, or no version, then the method must raise an INVALID_STATE_ERR exception. Fixed. On Mon, 26 May 2008, Chris Prince wrote: I think the spec is technically correct. What's confusing is that the same line can mean two different things: // Start with no databases. // On the 1st call, this line means create a database, // and set the version string to the empty string. var db1 = window.openDatabase(foo, , , ); // On the 2nd call, the meaning has changed to // open the 'foo' database, regardless of the version string. var db2 = window.openDatabase(foo, , , ); Yeah, that's a bit confusing. Not sure what to do about it. On Mon, 4 Aug 2008, Aaron Boodman wrote: It seems like you need a way to abort an in-progress transaction. An easy way to do this would be to add an abort() method to SQLTransaction. What's the use case? Can we wait until v2 to add this, or is it critical? On Mon, 4 Aug 2008, Aaron Boodman wrote: Currently, the database API has an error code for the situation where you open a transaction for read, then try to write but the database is locked. I think that the spec should at least suggest, but perhaps require, implementors to serialize access to a single database to prevent this from happening. Without this, developers must wrap every single write attempt in error handling and retry if a lock error occurs. It seems likely that developers will forget to do this and it will be a significant pain point with the API. Applications will seem to work, but then mysteriously fail in production when users have multiple copies of the application open. Even if the developer adds retry logic, it is easy for a page to get starved by another page. Serializing access would prevent all these problems at the cost of read concurrency, which I think is OK for the first version of this API. A future version of the API could add the concept of read transactions which would allow concurrency, but would fail immediately when you try to write with them. I am loath to do this, because of the performance hit. However, what we could do is say that if the first statement is read-only, they all have to be, and if the first statement is a write, then it locks the database? Though that would be rather weird... We could have a .writeTransaction() and a .readTransaction(), where the former always run in isolation. Any preferences? I'm fine