Stored Procedure introduction

A stored procedure is database code that runs on the server side, is generally invoked by a remote client, is precompiled, generally runs faster than the same set of code issued by the client and almost always leads to less network traffic between the client and server.

Values are passed in to the procedure, the procedure runs, and values are returned by the procedure to the caller.

The procedure is always called in the context of a database, thus when the procedure runs that database is open.

Stored procedures can perform many tasks. Although a stored procedure must be associated with a database, it need not in fact query, access, or modify the database.

The following are some examples of possible uses of stored procedures. This list is not by any means complete. The ability to define stored procedures is a very flexible tool with many uses.

Defining stored procedures

Stored procedures are defined in files which are uploaded to the server (they may be compiled or not before being uploaded). Once the compiled file is available to the server, new client sessions can specify, on startup, which stored procedure files to use. (You cannot upload a stored procedure and start using it after a session has started.)

Stored procedures have permissions which specify the users who can execute them. The permissions are some subset of rwWes.

s means superuser (so only superusers can run the stored procedure). If s is specified, the other permissions are effectively ignored since any superuser can run any stored procedure anyway, regardless of whether s is specified. (The purpose of the permission s is to restrict the procedure to superusers).

r means the user must have read permission, w write permission, e eval permission. The user must have all the permissions specified: so if the permissions are rw, only users with read and write permission can run the procedure. W means read or write. the default is rwW (the W is in fact redundant in the default case).

You must have superuser access to upload a stored procedure to the server.

In Lisp, stored procedures are defined with the macro def-stored-proc, which we define below.

Errors in stored procedures

Stored procedures are wrapped in code which catches signaled conditions (whether subtypes of error or others). If an error (or indeed any condition) is signaled, it is caught and the stored procedure terminates returning a vector whose first element is the string "_fail_" and whose second element is the error message (also a string).

Security issues with stored procedures

A stored procedure can do things to a database that the user running the stored procedure cannot himself or herself do. For example, a stored procedure could modify the database by adding and deleting triples even if the user does not have write permission. Indeed, this is one of the important uses of stored procedures: to give low-level users the ability to perform specific tasks modifying the database while protecting the database from other unauthorized modification by that user.

So when specifying permissions and user access, do remember that the usual permissions that restrict the actions of a user (such as no write permission) do not restrict the action of stored procedures the user can run (which might write to the database).

Only superusers can define stored procedures. Whatever protections you have to control and check what superusers do should be applied to their work defining stored procedures.

Lisp API

You can use Lisp to define a stored procedure. The following macro defines the procedure. The definition must be in a file which can be uploaded to the server.

def-stored-proc name  arglist  &body  body
macro

The macro defines a stored procedure, which is a routine which can be run by the AllegroGraph server when requested by a client with the appropriate permissions.

name should be a string or a symbol (in which case the symbol-name will be used).

arglist should be a list whose start is a Lisp function argument list (with certain restrictions) and which optionally ends with the lambda-list keyword &perm followed by one or more of the keywords :w, :r, :W, :e, :s, which specify the permissions needed for users to run the stored procedure.

The Lisp argument list can have required, optional and rest arguments, but not keyword or other types of arguments, or can have &whole followed by a symbol whose value will be a vector. So arglist looks like the following: 

(<required args> [&optional <optional args>] [&rest args] [&perm [:r] [:w] [:W] [:e] [:s]])

or like 

 (&whole argvec [&perm [:r] [:w] [:W] [:e] [:s]])

Here are a couple of examples: 

(a b c &optional d (e e-def) &perm :r :w :e)  
 
(&whole argvec &perm :r :w :e)

The default for permissions is :r :w :W. :W is equivalent to :r or :w which cannot be specified in any other way.

A user will be able to run the stored procedure if the user is a superuser or if the user's permissions are a superset of the specified permissions. So if &perm is :r :w, users with read and write permission can run the procedure but users with read but not write or write but not read permission cannot. To allow users with only one of read and write, specify :W as the value of &perm. If :s is included in &perm, only superusers can run the procedure.

Note that the permissions affect which users can run a stored procedure, not what the stored procedure can do. A stored procedure can write to the database even if &perm :r is the specified stored procedure permissions.

Only nil, strings, integers, lists, UPIs, and vectors whose values are nil, strings, integers, lists, UPIs or similar vectors can be passed as arguments to the stored procedure. (The actual communication between the AllegroGraph client and server is via a string, and those types of values can be converted to strings.) The &whole argvec style supports existing code but the more general format is more flexible and also triggers checking that the number of arguments passed is correct when the procedure is run. If you use the &whole argvec style, you must check that the vector is of the right size and has the right number of values in the stored procedure code.

The body is Lisp forms that implement the stored procedure.

Stored procedures should return an integer, a string, or a vector whose values are integers or strings or similar vectors.

If there is an error (or any other condition is signaled) when the stored procedure runs, the error (or other signal) is caught. The procedure then returns a vector whose first element is the string "_fail_" and whose second element is a string containing information about the failure (such as the error message).

Once defined, you can call a stored procedure using ag-call:

ag-call name-and-spec  &rest  args
function

Call a stored procedure defined by def-stored-proc.

  • name-and-spec specifies the name of the stored procedure and the specification used to find it on the server (see below).

  • args are the arguments passed to the stored procedure. These must all be serializable as strings (i.e., nil, strings, integers, and UPIs or lists and vectors composed of nil, strings, integers, UPIs, lists, and vectors).

name-and-spec is used to specify the stored procedure and how to find it on the server. If the session is started with stored procedures loaded (e.g., in an init file), then no additional information is needed to find the procedure and name-and-spec can be a symbol or string. Otherwise, name-and-spec must be a list of the form 

(procedure-name script-name &optional db-name &key scheme catalog user password server port)

If db-name etc. are not supplied, then the parameters used to find the server will be taken from the current triple-store (*db*)

Note that ag-call will only work with *db* if it is a remote-triple-store.

The following are all valid examples for name-and-spec:

  • 'remote-procedure-name
  • "remote-procedure-name"
  • '(remote-procedure-name "test-script.cl")
  • '(remote-procedure-name "test-script.cl" "my-db" :port 12345 :server "localhost")

ag-call will return the value from the stored-procedure. If there is an error, then it will return a vector whose first element is the string "_fail_" and whose other elements contain information about the error.

Procedure for using the Lisp API to define a stored procedure

The steps are as follows:

1. Write the stored procedure definition in a file.

The file can contain as many stored procedures definitions as you like, and also additional definitions as might be necessary.

2. Upload the file to the server.

You can do this with the HTTP API (PUT to /scripts/<filename>.cl) or using WebView as a Site Script. You can upload fasl files as well as source files. fasl files must be compiled with the same platform and Lisp version as the server uses.

For example, to use curl to upload a script named test-script.cl into a file named stored-proc.cl on the server, you could use: 

curl -X PUT --user USER:PASSWORD --data-binary @test-script.cl "http://HOST:PORT/scripts/stored-proc.cl"

Once the file is uploaded, you can see the available stored procedures with the HTTP GET command (GET to /scripts/). See HTTP Protocol for more on the general HTTP interface.

Running a Lisp stored procedure

Here is an example.

Define the stored procedure by uploading a .cl file which contains the following stored procedure definition (or by creating such a .cl file Sitescript in WebView): 

(def-stored-proc update-salary (person-name new-salary)  
  (let* ((person (resource (format nil "http://franz.com/example/person/#~a" person-name)))  
         (old-value (first (get-triples-list :s person :p !<http://franz.com/example/#salary>))))  
    (delete-triples :s person :p !<http://franz.com/example/#salary>)  
    (add-triple person !<http://franz.com/example/#salary> (literal new-salary))  
    (commit-triple-store)  
    (when old-value (object old-value))))

Then call it from a remote lisp client: 

(open-triple-store "foobar" :triple-store-class 'remote-triple-store  
                    :user "super" :password "super")  
(ag-call '(update-salary "testscript.cl") "Andreas" "92039")  
=> {2000}

JavaScript API

JavaScript stored procedures use the REST (Representational State Transfer) API. To create a JavaScript stored procedure, open the Scripts tab, and make sure JavaScript is selected for Language. Then, enter the code, for example: 

server.defineService("post", "changeSalary", function(request) {  
  var employeeName = request.param('employee');  
  var employee = '<http://franz.com/example/person/#' + employeeName + '>';  
  var oldStatements = store.getTriplesArray(employee,  
'<http://franz.com/example#salary>', null, null);  
 
  store.deleteTriples(employee, '<http://franz.com/example#salary>',  
null, null);  
  store.addTriple(employee, '<http://franz.com/example#salary>', '"' +  
request.param('salary') + '"');  
  store.commit();  
  if(oldStatements.length > 0) {  
    return oldStatements[0];    // return old salary  
  }  
});

Then enter a name (ending in ".js"), and select "Sitescript" in the "Save as" drop-down box. I'll assume you named it "jstest.js"

Now you have defined a JavaScript stored procedure. Now using the RESTful interface to such procedures, define it in a session at URL /custom/changeSalary that accepts two parameters (either via the HTTP query string or via the POST body), "employee" and "salary". It will look up the old value for the employee's salary, update the salary to the desired new salary and return the old value.

To use this web service, specify it when creating the session, like so (in the HTTP API): 

$ curl -X POST http://test:xyzzy@localhost:10035/repositories/foobar/session  
-d 'script=jstest.js'

(This uses the curl command line program to create a new session on the store "foobar", with the script that we saved above loaded.)

This will output a new URL which is the HTTP API endpoint for the session. With this, we can call the stored procedure: 

$ curl -X POST http://test:xyzzy@localhost:52787/custom/changeSalary -d  
employee=Andreas\&salary=20030

This sets the salary for employee "Andreas" to the value literal string "20030".