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.

• Modifying the database in a specified way. Suppose, for example, you have a database of employees and a new employee is hired. A stored procedure could take strings as arguments (first name, last name, salary) and create triples for the new employee (first name, last name, salary), assign an employee number, delete the ("company" "number of employees" "N") triple, and add the ("company" "number of employees" "N+1") triple, etc. And then commits the changes.

• Following on the first example, an advantage is the stored procedure takes strings as arguments and makes specific modifications to the database. The person entering the data may be a low-level clerk who does not know about database functionality. He or she just has a form which they fill in. The stored procedure does all the modification and ensures all necessary steps are performed.

• Suppose you are a lender. A borrower wants $1,000 at 5% for a year and wants to know the monthly payment. A stored procedure could calculate this. This is an example with no database access. It could of course be done in a separate program independently of AllegroGraph, but if AllegroGraph does the calculation, it will have exactly the same results if later the loan is made (such as same monthly payment -- a different program might produce slightly different results because of rounding).

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 should be in a file which can be uploaded to the server.

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

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

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

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.

Once the file is uploaded, you can see the available stored procedures with the HTTP GET command (GET to /scripts/). See HTTP Protocol - SPARQL Endpoint 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".