Re: [sqlite] stored procedures implementation for SQLite

[Andy Gibbs]

On Wednesday, January 26, 2011 5:38 PM, Chris Wolf wrote:

> 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
>

Yes, very interesting!

You approached the problem from a different angle to the way I did it.  I 
did it the other way round: created a procedural grammar that could be used 
like a standard statement, then found a way of storing them and then a way 
to call them.  As a result, I stored the procedures just inside 
sqlite_master, rather than having multiple additional tables.  I simply used 
wrappers around the sqlite3_bind_* functions for handling parameters to the 
stored procedures when they were called, rather than holding the parameter 
names in a table.  But I think your way is just as appropriate, and possibly 
better, in the long run.

Additional to calling a stored procedure through a standalone statement 
("EXEC fn(param)"), I also implemented calling through an SQL function (e.g. 
"SELECT exec(fn, param)"), which meant that the stored procedure could 
return a value that would be fed back into another statement/procedure.  Of 
course, that led to the possibility of recursive calls and other 
nightmares...

Anyway, I'll be interested to see how you progress.

Andy


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] stored procedures implementation for SQLite

[Chris Wolf]

Thanks for bringing that to my attention - that sample was left over from
when I was trying to use APSW rather then sqlite2.  The actual test program,
sqlite-3.7.3/src/createproc_test.c, is correct.  I updated the blog page
to reflect the proper code.

   -Chris

On Jan 26, 2011, at 12:55 PM, Jim Wilcoxson wrote:

> It looks interesting.  Should your except stmt reference apsw?  -Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> 
> 
> On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:
> 
>> 
>> 
>> 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
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] stored procedures implementation for SQLite

[Jim Wilcoxson]

It looks interesting.  Should your except stmt reference apsw?  -Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:

>
>
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] stored procedures implementation for SQLite

[Chris Wolf]

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  
>> 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