I know this is an old thread, but shortly after I read it, I attempted to implement stored procedures in SQLite! I only did it to see if I could, not because I necessarily think it's a good idea... It's very experimental and not fully implemented, but if anyone is interested, I checked in my work on GitHub, including pre-compiled binaries for MacOS and Linux.
http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended Regards, Chris Wolf BareFeetWare wrote: > On 13/11/2010, at 11:14 AM, Scott Hess wrote: > >> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <list....@barefeetware.com> >> wrote: >>> 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. >> From http://www.sqlite.org/about.html : >>> Think of SQLite not as a replacement for Oracle but as a replacement for >>> fopen() > > The full paragraph from that page reads: > >>> SQLite is an embedded SQL database engine. Unlike most other SQL databases, >>> SQLite does not have a separate server process. SQLite reads and writes >>> directly to ordinary disk files. A complete SQL database with multiple >>> tables, indices, triggers, and views, is contained in a single disk file. >>> The database file format is cross-platform - you can freely copy a database >>> between 32-bit and 64-bit systems or between big-endian and little-endian >>> architectures. These features make SQLite a popular choice as an >>> Application File Format. Think of SQLite not as a replacement for Oracle >>> but as a replacement for fopen() > > So I think it's referring to how SQLite stores its data in a local file, > rather than on a remote server with which it communicates indirectly. ie > "SQLite does not have a separate server process". In that way, SQLite is like > fopen rather than Oracle. The same paragraphs mentions SQLite "triggers, and > views", freely copying a [self contained] SQLite database between > architectures, which allude to my point about putting the logic in the > database itself so you can move the whole database between architectures. > >> So, yes, you might as well just be saving your data to a CSV or XML file. >> And I'm sure if you had a package to do that, someone would be arguing about >> whether your XML should allow for embedded transforms. > > What do you gain by implementing database logic in the application layer, > when it could be done far more efficiently and reliably in the SQL schema? > The only thing I can think of is avoiding the (shallow) learning curve. Why > re-invent and roll your own integrity checking etc when it's already > available and in a way much closer to the data than your application code can > get? > > See my previous post for the contrary argument: > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html > > Basically, database logic in the database itself is much faster, causes less > error, optimises queries, makes the database portable between application > environments or GUI apps. What's not to love? > > Thanks, > Tom > BareFeetWare > > -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
