On Fri, May 6, 2011 at 4:30 PM, Simon Slavin <[email protected]> wrote: > On 6 May 2011, at 10:14pm, Nico Williams wrote: >> Here's what I need: >> >> - at transaction time I need a way to record somewhere that the >> transaction did start. This would mostly be an insert into a table >> with an INTEGER PRIMARY KEY AUTOINCREMENT column, and a time of >> transaction start. I can do without this by simply doing an insert >> into that table if the relevant row didn't already exist. > > You can do an 'INSERT OR IGNORE'.
And INSERT INTO ... SELECT ... WHERE <sub-query to check whether the row doesn't already exist>. I know this -- it's what I mean above :) >> - at transaction commit time I need to be able to RAISE() exceptions >> if the concluding transaction does not meet certain requirements. >> I.e., SELECT RAISE(ROLLBACK, 'Error: user did not x, y, or z') WHERE >> <various sub-queries>. I have no way to detect end of transaction >> time, so I can't really do without this :( >> >> I'd also like to be able to do inserts/updates/deletes at transaction >> commit time, as if the application's COMMIT has been a savepoint, but >> I could live without this capability. > > You may be subverting the way SQL works. The alternative kind of trigger to > ROW is not TRANSACTION, it's STATEMENT. One can use several statements in a > TRANSACTION. Well, D.R. Hipp at least at one point disagreed with you, since he himself proposed something like this: http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html Moreover, other SQL RDBMes support transaction triggers. See, for example: http://en.wikipedia.org/wiki/Database_trigger Finally, arguably triggers alone violate the SQL concept of a declarative language. I've shown here before how one can do quite a bit of procedural programming using triggers in SQLite3. So if it's SQL purity you want, well, it's too late already :) I grant you that one might not want to further depart from the pure than one already has, but that's a different argument than "you're leaving the pure behind". > I suspect your easiest way forward would be to implement that code inside > your application: instead of calling "BEGIN" and "COMMIT" make your own > routines for those two things. Another way would be to take apart SQLite and > rewrite some of the code in ways that suit this one particular application. One of the very nice things about SQLite3 is the availability of the shell, and the fact that anyone can download SQLite3 and link it into any application. In the particular application I'm building I'd like to be able to encode as much as possible of the business logic into the schema so that I don't have to forbid direct access to the DB by any programs other than those I write. The reason for this is that I very much foresee third parties wanting to write their own tools to manipulate my application's database. Moreover, I don't want to write all those tools, as that's not what I'm getting paid to do -- enabling third parties here is of great utility to me. I believe that would be a great feature for my application to have. All I need is a way to run some SELECT statements at COMMIT time, with those SELECTs possibly RAISE()ing exceptions. I could use more functionality too, but that's the bare minimum I need. I can detect transaction start, but not transaction end. I need to detect transaction end... Nico -- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
