Simon Slavin wrote: > On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >> Stored Procedures > > How do those differ from what can be done with triggers ?
A stored procedure is an arbitrary-sized named sequence of statements to execute, which is stored in the database as data (same as table or view or trigger definitions), and which generally is explicitly invoked as a statement. A trigger is a stimulus-response rule that says when a particular event happens then a particular stored procedure is to be executed automatically. In the general case, this is like an event handler in a typical application that responds to mouse clicks or network connections or whatever. Some DBMSs support this in the more general sense of "do this when this happens" but most DBMSs that support "triggers" just handler more limited situations, such as "do this before/after a record is inserted/updated/deleted in this table". ---------- As for my own wishlist, well I'll name a few items. 1. If I were asked a year ago I would say top of the list is support for child transactions, but these were already delivered in 3.6.8 so my greatest wish is already met. 2. While stored procedures would be very valuable, I do not see them necessary for SQLite itself to implement, since these at least can be effectively done at the user level, especially when you consider that you would want to have parameters and variables so to either use the same user input with multiple statements in the procedure or feed results of one statement to input to another, and you'd probably want conditionals or loops etc, which host languages already provide for your use. 3. I would like to see a pragma and/or compile-time option (or several for finer graining) that subtly alters some SQL semantics or allowed syntax when in use, to deal with some SQL mis-features. 3a. I would like to have the option for SQLite to always operate using set semantics rather than bag semantics, automatically, so that for example any join or union or select-list or group or count() or aggregate etc would always just return unique rows and never treat duplicates as being distinct. Using this mode would first of all be more likely to give the results that users actually want, avoiding common bugs, and also allow for the SQLite query optimizer to be much more efficient as it could safely make more rearrangements of the query without worry that doing so would change the answer in the presence of duplicates. 3b. I would like to have the option for SQLite to always operate using 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply treated as another value of its own singleton type that is disjoint from all other types same as Integer, Numeric, Text, Blob are disjoint. And so, one could then just use ordinary equality or not-equality tests to check for NULL, and NULL would equal NULL but not equal anything else, and boolean tests would always return true or false, not null. Once again, this would mean that behavior is more like what users actually expect and bugs can be avoided, and the query optimizer can be more efficient again, allowing more reorganization knowing at answers wouldn't change due to this. 3c. I would like to have the option for SQLite to never have duplicate unqualified column names; for example, if one said "foo NATURAL INNER JOIN bar" then only a single column with the common data would be in the result, rather than 2; likewise for inner joins with explicit join conditions of "foo.a = bar.a" would just return a single "a" in the result. Such things as this, especially 3a,3b, *are* best implemented at the internal guts level of SQLite, for what is hopefully obvious reasons. Now writing SQL that targets these semantics may not be fully portable, but it would be a lot more correct and trouble-free for people just using SQLite, or other DBMSs that support those semantics I proposed. And a point is that an implementation of what I proposed would be *simpler*/*liter* than what is required to implement the standard SQL semantics that support duplicates and 3VL, and it generally does what people actually want. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
