On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote: > "jeff archer" schrieb >> From: "Olaf Schmidt" >> Wednesday, November 10, 2010 9:07:19 AM >> >>> [Stored procedures in SQLite] >>> >>> IMO stored procedure-support only makes sense in "Server-Instances" which >>> run on their own... > >> I disagree. The overall design and structure of applications using SQLite >> and therefor SQLite itself would benefit from SQLite supporting stored >> procedures. This would allow all code necessary for enforcing the business >> rules of the data to be stored in the database itself. This is just a good >> basic design principal. > > There was a somewhat similar sounding post (from BareFeetWare, sent on 20.Oct > to this list) who also encouraged, to include "more logic" into the > SQLite-Files itself, to reach more "portability".
That post of mine is archived here: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html For the record, I (for BareFeetWare) was advocating implementing database logic in constraints and triggers, rather than in application code. I was not actually talking about stored procedures. In order of preference, I would implement logic in: 1. Constraints 2. Triggers 9. Pure SQL (eg stored procedures) 10. Application code (ie an external language that calls SQL snippets) IMO, if you're implementing database logic (ie constraints and triggers) in application code, then you're reinventing the wheel, making your package unnecessarily complex and grossly inefficient. If you're just using SQLite to store your data but doing all the logic outside of SQLite, then you might as well just be saving your data to a CSV file or XML. See my previous post for reasoning behind this. Now, as for stored procedures (which I wasn't discussing before), I will chime in on this new discussion: It is fairly trivial to add your own table to your database for storing procedures, such as: create table "Procedures" ( ID integer primary key , Name text unique , Description text , "SQL" text ) ; In this you can store and name SQL procedures, using whatever external application code to simply get the SQL of the desired Procedure by Name and execute it. It will run as one connection within SQLite, utilise the begin commit/rollback mechanism etc. You can combine, say, an update and select, to update one table based on results from another very efficiently totally within SQL, without reinjecting the results of one query into a second query via an application code layer. And so on. Such an after market stored procedure mechanism isn't as good as could be achieved via a native stored procedure facility built into SQLite, but it fulfils many of the objectives. It does lack variables, procedural nesting etc, but those can usually be accomplished by a set theory approach (which is desirable anyway) and a similarly contrived "Variables" table. It also has to convert raw text SQL into object/machine code on each call. But my question is: why? Why would we want to perform stored procedures? Or why would we want to perform application code, if you're on that side of the "war"? ;-) In most cases, I suggest that you should be implementing your database logic in constraints and triggers, not in procedural code (ie not in SQL store procedures and not in application code). If you have code (in either environment) that is looping or grabbing one result and sticking it in a second query, then your approach is probably flawed. You really should be approaching the data as sets and not something over which code should be iterated. Having said that, there are some needs for procedural code that aren't just misplaced and inefficient attempts to implementing internal logic. For example, I have procedural scripts that: 1. Import data from one table to another (eg in a different database). 2. Periodically clean, flush or delete redundant or outdated entries from a logging table 3. Change the data in one table to match a data set proposed by best estimate calculations (eg budget forecasts or assignments based on most recent choices) They do implement "business logic" but not what I would call "database logic". These procedures are actions initiated by the user or business schedule, such as importing a new sales catalog, end of cycle data cleansing, or a preliminary entry of "best guess" data before the data entry operator or bookkeeper manual enters hundreds of records. These procedures are triggered by an external event. By contrast, database logic implements data integrity and is initiated by an internal event such as an update, insert or delete. If your experience differs to this, please enlighten me. Do you have or need procedures (either stored SQL or in application code) that aren't one of: 1. Enforce data integrity such as restricting entries or cascading changes, which could/should be instead accomplished through constraints and triggers. or 2. External event driven business logic or scheduled tasks such as importing new data, cleaning out old data or proposing completion of new data based on history. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
