scripting language interpreter

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

scripting language interpreter

Tim Romano-4
In part, this is a very broad question but I hope it's not unacceptable.
From a birdseye perspective, what is involved in integrating an interpreted
scripting language with a database engine?  I am aware that SQLite supports
loadable extensions, but would the SQLite architecture also permit the
integration of an interpreted scripting language?   An integrated scripting
language makes an already powerful database engine orders of magnitude more
useful especially when one is solving ad hoc data problems requiring very
rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of
any project pursuing such an integration?

Regards
Tim Romano
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Simon Slavin-3

On 1 May 2010, at 2:25pm, Tim Romano wrote:

> In part, this is a very broad question but I hope it's not unacceptable.
> From a birdseye perspective, what is involved in integrating an interpreted
> scripting language with a database engine?  I am aware that SQLite supports
> loadable extensions, but would the SQLite architecture also permit the
> integration of an interpreted scripting language?   An integrated scripting
> language makes an already powerful database engine orders of magnitude more
> useful especially when one is solving ad hoc data problems requiring very
> rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
> MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know of
> any project pursuing such an integration?

SQLite already contains three scripting elements.  The first is TRIGGERs, where you can supply a sequence of operations to be completed when certain things happen.  The second is FOREIGN KEY support, which is more restricted in terms of what can be done, but easier to understand.  And the third is the COMMIT/ROLLBACK system which can also be considered a method of pre-programming certain events.

One problem with implementing scripting within a database language is that it turns into just another layer of complication.  If you migrate some of your application logic into the scripting language it gives you another layer of stuff to debug.  I already don't know how a value arrived in one of my fields: is it a default value for that column ?  Or did it arrive there via a TRIGGER ?  Or was it explicitly put in there by my application ?  If you add another layer to that you're complicating an already complicated set of possibilities.

Another problem with it is that sooner or later you need your inner language (your SQL engine) to have access to your outer environment, for example, to find out if you have lots of filespace free.  At that point you have to start specifying things about your OS (for example, that it actually does have a file system and you're not just using memory).  Which means you're going to restrict yourself to only some implementations of SQLite.  Which then means SQLite either has to have forks or waste code in environments it's not suitable for.  That way madness lies.

So the conventional way to handle it is to put your SQL engine near the bottom of the heap, just above the file system.  If you want it to act as if it has a scripting language, implement your own, and call SQLite only by your own calls rather than directly.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Tim Romano-4
Simon,

It's not clear to me how this is a result of scripting language support:

"Another problem with it is that sooner or later you need your inner
language (your SQL engine) to have access to your outer environment, for
example, to find out if you have lots of filespace free."

Could you please elaborate?

The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in
mind when I used the phrase "scripting language". I am thinking of
full-blown procedural logic integrated with the RDBMS. The foremost example
that comes to mind is VBA in MS-Access, though many other examples are
available, e.g. Revelation, Borland Paradox, or even java in Oracle.

Now, comparing SQLite with those other databases is like comparing a giraffe
with an alligator and then complaining that the alligator cannot reach the
tall acacia fruits or that the giraffe cannot take down a water buffalo
because he cannot fully submerge and his teeth are too dull.  I am very
aware of SQLite's many strengths and how it is different from those other
databases.  But I'd still like SQLite to have an integrated interpreted
scripting language if that were possible, because I know from experience
that if the interpreted scripting language integration is well handled you
can end up with the best of both worlds: SQL set logic and procedural logic
with arrays/collections.  That is of course one of the main reasons for the
loadable extensions module in SQLite. An important difference is that the
scripting language can be "sandboxed" and prevented from referencing the
file system.  If support for an interprested scripting language were
available,  Google's and Adobe's and other similar implementations of SQLite
could have access to the sort of power that loadable extensions offer.

Regards
Tim Romano


On Sat, May 1, 2010 at 10:04 AM, Simon Slavin <[hidden email]> wrote:

>
> On 1 May 2010, at 2:25pm, Tim Romano wrote:
>
> > In part, this is a very broad question but I hope it's not unacceptable.
> > From a birdseye perspective, what is involved in integrating an
> interpreted
> > scripting language with a database engine?  I am aware that SQLite
> supports
> > loadable extensions, but would the SQLite architecture also permit the
> > integration of an interpreted scripting language?   An integrated
> scripting
> > language makes an already powerful database engine orders of magnitude
> more
> > useful especially when one is solving ad hoc data problems requiring very
> > rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
> > MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know
> of
> > any project pursuing such an integration?
>
> SQLite already contains three scripting elements.  The first is TRIGGERs,
> where you can supply a sequence of operations to be completed when certain
> things happen.  The second is FOREIGN KEY support, which is more restricted
> in terms of what can be done, but easier to understand.  And the third is
> the COMMIT/ROLLBACK system which can also be considered a method of
> pre-programming certain events.
>
> One problem with implementing scripting within a database language is that
> it turns into just another layer of complication.  If you migrate some of
> your application logic into the scripting language it gives you another
> layer of stuff to debug.  I already don't know how a value arrived in one of
> my fields: is it a default value for that column ?  Or did it arrive there
> via a TRIGGER ?  Or was it explicitly put in there by my application ?  If
> you add another layer to that you're complicating an already complicated set
> of possibilities.
>
> Another problem with it is that sooner or later you need your inner
> language (your SQL engine) to have access to your outer environment, for
> example, to find out if you have lots of filespace free.  At that point you
> have to start specifying things about your OS (for example, that it actually
> does have a file system and you're not just using memory).  Which means
> you're going to restrict yourself to only some implementations of SQLite.
>  Which then means SQLite either has to have forks or waste code in
> environments it's not suitable for.  That way madness lies.
>
> So the conventional way to handle it is to put your SQL engine near the
> bottom of the heap, just above the file system.  If you want it to act as if
> it has a scripting language, implement your own, and call SQLite only by
> your own calls rather than directly.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Puneet Kishor-2
On Sat, May 1, 2010 at 9:53 AM, Tim Romano <[hidden email]> wrote:

> Simon,
>
> It's not clear to me how this is a result of scripting language support:
>
> "Another problem with it is that sooner or later you need your inner
> language (your SQL engine) to have access to your outer environment, for
> example, to find out if you have lots of filespace free."
>
> Could you please elaborate?
>
> The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in
> mind when I used the phrase "scripting language". I am thinking of
> full-blown procedural logic integrated with the RDBMS. The foremost example
> that comes to mind is VBA in MS-Access, though many other examples are
> available, e.g. Revelation, Borland Paradox, or even java in Oracle.
>


You do it the other way around... instead of embedding a scripting
language inside SQLite (which would convert it to
SQLVeryHeavyAndCumbersomeAndLikelyToBlowUp), you embed sqlite inside a
scripting language. See how elegantly this is done with Perl and
DBD::SQLite which embeds its own sqlite engine in the perl module.
Similar products exist for Tcl and Python and other languages.

.. <snipped> ..


--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Jay Kreibich
In reply to this post by Tim Romano-4
On Sat, May 01, 2010 at 09:25:54AM -0400, Tim Romano scratched on the wall:

> In part, this is a very broad question but I hope it's not unacceptable.
> From a birdseye perspective, what is involved in integrating an interpreted
> scripting language with a database engine?

  A number of people have looked into adding some kind of PL/* language
  to SQLite so that you can store functions, etc., in the database
  itself.  There are third-party folks working on a PL/SQL extension
  that converts stuff to compiled code, but that's a bit different
  since it isn't tied directly to the database.

  It is extremely unlikely anything would ever end up in the core.
  The biggest issue is license.  I know of no public-domain scripting
  language implementations (that are worth using).  Language systems
  also tend to be big and tricky to test.  So you'd still need the
  correct extension, but you could use the same extension for every
  database.

  Adding support for functions, aggregates, and collations is easy
  enough.  Many of the scripting language API wrappers already include
  stubs that allow functions, etc., to be defined in the scripting
  language.  Triggers are a bit more difficult, unless you can can
  reduce the logic down to a function.  The issue is passing the row
  state in and out of the function.  There may be some clever way of
  doing it deeper in the library, but I'm not sure it is possible with
  the public API.



  I'd love to see a Lua extension.  I know that's been proposed. Lua is
  MIT licensed, small, fast, and reasonably mature.  It is also designed
  to be easily embeddable, making it a good fit. I realize many of you
  may not know about Lua, but it is heavily used in a number of specific
  areas, including the game industry, where it is *the* behavioral
  and AI language, mostly because it is easy to learn, easy to embed,
  and will compile on just about anything.      http://www.lua.org/

    -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Richard Hipp-3
In reply to this post by Tim Romano-4
On Sat, May 1, 2010 at 9:25 AM, Tim Romano <[hidden email]> wrote:

> I am aware that SQLite supports
> loadable extensions, but would the SQLite architecture also permit the
> integration of an interpreted scripting language?   An integrated scripting
> language makes an already powerful database engine orders of magnitude more
> useful especially when one is solving ad hoc data problems requiring very
> rapid turnaround.
>

See http://www.sqlite.org/tclsqlite.html for the original.  SQLite began
life as a TCL extension.  In fact, we often think of SQLite as a TCL
extension that escaped into the wild.

The integration between TCL and SQLite is very tight.  If you know where to
look, you will see that many features of SQLite were designed specifically
to support integration with TCL.

An example of TCL using SQLite:

  db eval {SELECT name FROM people WHERE personid=$personid} {
     puts name=$name
  }

The "db" is an TCL object which represents an open SQLite database
connection.  "eval" is the "evaluation" method.  Within the SQL text, we see
a TCL variable $personid.  That is really implemented as an SQL parameter,
but the TCL wrapper knows to loop over all SQL parameters, look up the
corresponding TCL variables, and bind them.  The stuff that follows is a
script that runs for each row returned.  The columns of the SELECT statement
are stored in TCL variables making them very easy to access.

If you want to compile a TCL interpreter than includes SQLite, it is simple
to do.  Just obtain the tclsqlite3.c amalgamation (available on the download
page) and run this:

     cc -o tclsh -DTCLSH=1 tclsqlite3.c -ltcl -ldl -lpthread -lm

You might have to adjust the list of libraries at the end of the compiler
command-line, but that is basically all that is required.  The resulting
"tclsh" is a script interpreter that includes full SQLite integration.

Yes, SQLite can be bound to other scripting languages.  But it was designed
for TCL and the integration with TCL is very tight, seamless, and easy to
use.  In all other wrappers that I am aware of, the binding of parameters
and the looping over result rows are separate explicit steps, not built into
the language the way their are with TCL.  A tclsh with integrated SQLite
support is a very convenient tool to have at hand for many common tasks.

--
---------------------
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Gabor Grothendieck
On Sat, May 1, 2010 at 12:15 PM, Richard Hipp <[hidden email]> wrote:

> On Sat, May 1, 2010 at 9:25 AM, Tim Romano <[hidden email]> wrote:
>
>> I am aware that SQLite supports
>> loadable extensions, but would the SQLite architecture also permit the
>> integration of an interpreted scripting language?   An integrated scripting
>> language makes an already powerful database engine orders of magnitude more
>> useful especially when one is solving ad hoc data problems requiring very
>> rapid turnaround.
>>
>
> See http://www.sqlite.org/tclsqlite.html for the original.  SQLite began
> life as a TCL extension.  In fact, we often think of SQLite as a TCL
> extension that escaped into the wild.
>
> The integration between TCL and SQLite is very tight.  If you know where to
> look, you will see that many features of SQLite were designed specifically
> to support integration with TCL.
>
> An example of TCL using SQLite:
>
>  db eval {SELECT name FROM people WHERE personid=$personid} {
>     puts name=$name
>  }

If I understand correctly what is being illustrated here then the
sqldf package in R (http://sqldf.googlecode.com) has a similar
facility.  For example. from the R command line:

> # installs everything needed into R
> install.packages("sqldf")
>
> # loads everything needed into R workspace
> library(sqldf)
>
> # create R data frame (similar concept to an SQL table)
> DF <- data.frame(a = 1:3, b = 4:6)
>
> # the next statement notices that DF is an R data frame,
> # it automatically creates an sqlite data base in memory,
> # sets up table definition for DF by issuing create table stmt,
> # loads DF into the sqlite data base,
> # performs the query returning a new data frame
> # and deletes the sqlite data base
>
> sqldf("select * from DF where a < 3")
  a b
1 1 4
2 2 5

The actual interfaces between R and sqlite is in the DBI and RSQLite R
packages and sqldf sits on top of those.  The RSQLite package also
includes a copy of sqlite.  Installing and loading sqldf automatically
installs and loads its dependencies.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Tim Romano-4
In reply to this post by Jay Kreibich
Very cool, Jay.

Regards
Tim Romano


On Sat, May 1, 2010 at 11:23 AM, Jay A. Kreibich <[hidden email]> wrote:

>
>
> <snip>
>  I'd love to see a Lua extension.  I know that's been proposed. Lua is
>  MIT licensed, small, fast, and reasonably mature.  It is also designed
>  to be easily embeddable, making it a good fit. I realize many of you
>  may not know about Lua, but it is heavily used in a number of specific
>  areas, including the game industry, where it is *the* behavioral
>  and AI language, mostly because it is easy to learn, easy to embed,
>  and will compile on just about anything.      http://www.lua.org/
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Tim Romano-4
In reply to this post by Richard Hipp-3
@DRH  : Thank you for the info on SQLite with TCL.
Regards
Tim Romano


>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Alexey Pechnikov-2
In reply to this post by Tim Romano-4
As I think, WClark work on stored procedures implementation for SQLite
3.x. But I don't know about current state of this job.

You can see some of his patches here:
http://sqlite.mobigroup.ru/src/wiki?name=WClark

Note: I'm using the stored TCL procedures but native stores procedures
support will be fine.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Simon Slavin-3

On 2 May 2010, at 2:34pm, Alexey Pechnikov wrote:

> As I think, WClark work on stored procedures implementation for SQLite
> 3.x. But I don't know about current state of this job.

Ah.  Stored procedures are certainly doable.  For instance JavaScript supports a little-used function called 'eval' which runs JavaScript code passed to it.  You can store your JavaScript code in a SQL field, of course.  So with a trivial framework around a table you can tightly bind SQLite and JavaScript.

Needless to say, this is a terrific way to introduce security vulnerabilities.  Put these two together with ATTACH and you're in deep deep trouble.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Tim Romano-4
Since Javascript has been mentioned:

http://code.google.com/apis/v8/intro.html

Tim Romano
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Alexey Pechnikov-2
In reply to this post by Simon Slavin-3
I did speak about stored procedures compiled into native SQLite opcodes.

P.S. I did see
1) the compiler (from C# or other language, I'm not sure) into SQLite opcodes
2) stored procedures realization for SQLite 2.x.


2010/5/2 Simon Slavin <[hidden email]>:
> Ah.  Stored procedures are certainly doable.  For instance JavaScript supports a little-used function called 'eval' which runs JavaScript code passed to it.  You can store your JavaScript code in a SQL field, of course.  So with a trivial framework around a table you can tightly bind SQLite and JavaScript.

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: scripting language interpreter

Darren Duncan
In reply to this post by Tim Romano-4
> In part, this is a very broad question but I hope it's not unacceptable.
> From a birdseye perspective, what is involved in integrating an
> interpreted
> scripting language with a database engine?  I am aware that SQLite
> supports
> loadable extensions, but would the SQLite architecture also permit the
> integration of an interpreted scripting language?   An integrated
> scripting
> language makes an already powerful database engine orders of magnitude
> more
> useful especially when one is solving ad hoc data problems requiring very
> rapid turnaround.  SQlite with, say, an ActionScript interpreter (ala
> MS-Access->VBA) would be an amazingly powerful desktop tool. Do you know
> of
> any project pursuing such an integration?

Integrating the relational DBMS and the expressive Turing-complete
programming language is what my Muldis database project is largely about.
See http://search.cpan.org/dist/Muldis-D/ for the spec, now at version
0.124.0.  Its designed to scale and work well with feature sets ranging
from SQLite to Oracle.  I'm halfway through hitting a major milestone now,
0.124.0 being the first half of that, and if its not inappropriate I'll
send an announcement to the SQLite list following the second half in a
couple days. -- Darren Duncan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users