[sqlite] Savepoint Questions
Hello all, I have some questions and comments about savepoints, documented here: https://www.sqlite.org/lang_savepoint.html Here are some excerpts from that documentation that are relevant to my questions/comments: 1. "The SAVEPOINT command starts a new transaction with a name. The transaction names need not be unique." 2. "The ROLLBACK command with a TO clause rolls back transactions going backwards in time back to the most recent SAVEPOINT with a matching name. The SAVEPOINT with the matching name remains on the transaction stack, but all database changes that occurred after that SAVEPOINT was created are rolled back." Also (not documented on that page): 3. Savepoint names cannot be parameterized in statements, which means you cannot prepare a savepoint statement ahead of time, and supply the savepoint name at execution time. Through some experimentation (using v3.11.0), I have found that #1 and #2 together don't work very well: begin transaction; ... Work A ... savepoint Foo; ... Work B ... savepoint Foo;<--- Allowed by #1 ... Work C ... rollback to savepoint Foo;<--- Rolls back Work C, but leaves the innermost savepoint Foo active due to #2 rollback to savepoint Foo;<--- Does nothing - I can no longer rollback past the innermost Foo without rolling back the entire transaction. Questions: A) If #2 is desirable, then why allow non-unique savepoint names within the same transaction? It is impossible to rollback the innermost savepoint with a non-unique name, and later rollback past that savepoint using the same name. B) If #1 is desirable, then why doesn't ROLLBACK TO pop the matched savepoint off the stack? Even without considering #1, I'm having trouble coming up with a scenario in which I would want the matched savepoint to remain active. C) If #1 and #2 are desirable, then why can't I parameterize savepoint names? This would allow me to prepare my savepoint statements once, and execute them with different names, gaining the efficiencies of prepared statements for these oft-used actions. Currently, I am forced to generate unique savepoint names _and_ prepare new statements every time I want to create a new savepoint, which is doubly inefficient. Thoughts? -Paul ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specifying a nullable column
On 12-Jun-2012 08:08, Richard Hipp wrote: On Tue, Jun 12, 2012 at 7:04 AM, Paul Medynski<pmedyn...@rim.com> wrote: Hi Kevin, I understand the 'NOT NULL' column constraint and the syntax diagram and text describe it quite well. What isn't described is whether or not specifying simply 'NULL' is truly supported. For example, the following create table statement is accepted by SQLite and appears to behave as expected (column 'Id' is not null and column 'Name' is nullable): create table ( Id integer not null, Name text null ); I would like to know if 'NULL' is meant to be a valid column constraint or not. It seems to work, but the documentation doesn't mention it explicitly. The NULL constraint is parsed and then ignored. http://www.sqlite.org/src/artifact/f29df90bd3a?ln=299 Perfect! Thanks Richard. -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Specifying a nullable column
Hi Kevin, I understand the 'NOT NULL' column constraint and the syntax diagram and text describe it quite well. What isn't described is whether or not specifying simply 'NULL' is truly supported. For example, the following create table statement is accepted by SQLite and appears to behave as expected (column 'Id' is not null and column 'Name' is nullable): create table ( Id integer not null, Name text null ); I would like to know if 'NULL' is meant to be a valid column constraint or not. It seems to work, but the documentation doesn't mention it explicitly. Thanks, -Paul On 11-Jun-2012 15:35, Kevin Benson wrote: On Mon, Jun 11, 2012 at 10:35 AM, Paul Medynski<pmedyn...@rim.com> wrote: Hi folks, I notice that the syntax diagram for 'create table' shows the 'column-constraint' definition as requiring 'null' to always be preceded by 'not'. I don't see any definition that supports just 'null'. However, when using SQLite3 3.7.3 or 3.7.9, I can create a table and specify any column as "Foo null", and it works as expected allowing the column to contain null values. Is the syntax diagram simply out of date, or am I doing something that appears to work, but will bite me in the end? :) http://www.sqlite.org/draft/lang_createtable.html "A CREATE TABLE command specifies the following attributes of the new table: - - - - - A default value or expression for each column in the table. - - - A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints." "A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL." -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- ---- Paul Medynski BlackBerry Messenger Group Senior Software Developer Research In Motion pmedyn...@rim.com50 Innovation Drive Phone: +1 902 982 6134 Bedford, NS Fax:+1 902 482 4380 B4B 0G4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Specifying a nullable column
Hi folks, I notice that the syntax diagram for 'create table' shows the 'column-constraint' definition as requiring 'null' to always be preceded by 'not'. I don't see any definition that supports just 'null'. However, when using SQLite3 3.7.3 or 3.7.9, I can create a table and specify any column as "Foo null", and it works as expected allowing the column to contain null values. Is the syntax diagram simply out of date, or am I doing something that appears to work, but will bite me in the end? :) Thanks, -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
