CHARP Remote Procedures - pupitetris/charp GitHub Wiki
Writing remote procedures is pretty straightforward. You just need to create a SQL function whose name starts with the prefix rp_; the function can be declared in any of the languages supported by PostgreSQL or native SQL.
For most purposes, we recommend the use of plpgsql, since executing queries and obtaining results feels more natural than with other languages. When operations outside database querying and manipulation are required, we recommend the use of plpython.
It is recommended to store SQL functions in the sql/05-functions.sql file.
All remote procedures must start their name with the prefix rp_ to be found by CHARP. When a remote procedure is requested from the web, CHARP prepends the requested resource with the string rp_. This is a security feature that prevents other functions from being called from the outside.
Afther the rp_ prefix, functions may have other prefixes to indicate special functionality:
The anon_ prefix may be used to indicate an anonymous function. These functions expose system functionality or data that may be available to the general public. You should use anon functions whenever possible because they skip the authentication steps and so they are completed within a single HTTP call.
For an anon function to be called, the HTTP POST request must come with a parameter named anon set to a non-empty string. CHARP will automatically prepend the anon_ prefix to the name of the resource. So for example, a call to test_me at the HTTP level with the anon parameter set will call the stored procedure named rp_anon_test_me. This naming scheme is enforced to again guarantee that no remote procedure is called anonymously from the outside, unless the developer explicitly declares them with the rp_anon_ prefix.
It is also possible to call anonymous functions by using !anonymous as a username.
Typical CHARP Remote Procedures return JSON data to the client, but the file_ prefix may be used to indicate that the function will return raw data. This raw data can be used as an HTML object source, such as an image, a script or many other external resources. These functions are expected to return a single record with two text columns: mimetype and filename. mimetype will reflect the file type that is being returned, to be used for the Content-Type HTTP header. filename is the location of a file that CHARP will read and send as the reply's body. The Content-length header is also set accordingly.
* TODO: A data column may be desirable for content generated from within the stored procedure; especially from within plpython routines.
Both prefixes may be combined, using rp_anon_file_, to offer anonymous raw data results.
CHARP automatically inspects the database functions and tries to find the function that the HTTP request is asking for. If it is not found, the SQL:PROCUNK exception is raised and sent back to the client.
When authentication is successful, CHARP inspects the stored procedure that is about to be called and compares the number of parameters required with those that have been sent through HTTP. The parameters are then converted to the corresponding type and a SELECT is made on all the columns FROM the result of the corresponding function. If the number of parameters does not correspond to the remote procedure found, the CGI:NUMPARAM exception is raised.
CHARP supports the following types:
| CHARP | JSON | SQL |
|---|---|---|
| INT | number | integer |
| STR | string | varchar |
| BOOL | number | boolean |
| DATE | string | date |
| INTARR | [number,...] | integer array |
| STRARR | [string,...] | varchar arrray |
| BOOLARR | [number,...] | boolean array |
| UID | n/a | charp_user_id |
Unrecognized types are treated as STR. Of relevant notice is that the SQL bigint type is not recognized, and so, it is treated as STR.
When you specify an input variable in your stored procedure of type charp_user_id, CHARP provides the account_id associated to the record in the account table for the provided login. Since the value is found automatically, the number of required parameters is reduced by one for every charp_user_id input required. Of course, functions that require this type of parameter should only ask for it once, otherwise the same datum would be provided multiple times, which is quite useless. Also, it doesn't make sense to require this parameter for an anonymous function, since the HTTP petition is not required to include a login parameter for those remote procedures. As a convention, if a charp_user_id is required, it is specified as the first parameter for the function with the name of _uid.
Remote procedures may return a single value or multiple rows of table values. Either way, the CHARP response is always a fields value with an array with the names of the returning fields, and a data value with an array of arrays with the resulting rows. Remote procedures that return a single value are a particular case of this, having a single field with the name of the remote procedure that was called, and a single row with a single column with the returned value.
Take into account that CHARP only takes care of authentication, but not of authorization. CHARP only checks to see if the user really exists and if it has the credentials to call remote procedures inside the system. It is the responsibility of the programmer to code routines to be called at the start of each remote procedure to discover if the user is cleared to perform the operations that are about to take place. CHARP provides a couple of exceptions for authorization failures: SQL:USERPERM, to report that the user is not cleared to execute the procedure in question, and SQL:USERPARAMPERM, to report that the user cannot operate on the information provided, even if it is allowed to run the requested function.
Exceptions can be fired from inside the database functions to interrupt their execution. CHARP provides its own exception firing routine, which packs useful information on the exception message so that it can be wrapped up in JSON and sent back to the client where it is easily processed. Use charp_raise to raise exceptions. Usage:
charp_raise (''exception_type'', ''arg1'', ''arg2''...)
exception_type is a string literal that specifies what kind of exception has occurred. This key will arrive to the client in the error object where the application will react accordingly.
arg1, arg2 ... are a variable number of arguments that will be used to compose the human-readable error message. These arguments must be of a type that can be cast to TEXT.
Unhandled non-synthetic SQL exceptions (say, a constraint violation) are picked up at the CHARP RPC and also packaged on a JSON hash just like any other CHARP exception, using the DBI:EXECUTE key and allowing for easy debugging and support.
All exceptions are sent to the error_log table (using 02-charp.sql:charp_log_error). Logging can be omitted for exceptions generated with charp_raise using a minus sign (-) as a prefix, for those cases when an exception is part of the expected workflow of the application. Example:
-- Exit remote procedure call sending code 23 back to the application without logging:
charp_raise ('-EXIT', 23);
All exceptions generated by the database should be caught within the remote procedure and it should react accordingly. It is a bad practice to handle exceptions at the client. All non-synthetic exceptions that are not caught are logged as they are considered anomalous application behaviour.
Check the file site/cgi/CHARP.pm for a reference of all possible errors. Error keys have a prefix that tell the level at which the error ocurred: HTTP (connection), CGI (Common Gateway Interface parameters and processing), DBI (database connection) and SQL (inside stored procedures, charp_raise and unhandled native exceptions).
Errors also have a severity level, which helps the system prepare a message for the user on how to handle the situation: INTERNAL (system bugs), PERM (authorization), RETRY (try again later), USER (provided data), EXIT (exit status, should always be handled by app, not really an error).
These are the exceptions that may be fired under different situations from inside the stored procedures (level SQL):
| Exception | Parameters | Description | Example |
|---|---|---|---|
| ASSERT | condition | A required invariant for the called routine is violated. | PERFORM charp_raise('ASSERT', 'Divisor != 0'); |
| USERPERM | The user is not authorized to call the routine. | PERFORM charp_raise('USERPERM'); | |
| USERPARAMPERM | conflicting data | The user cannot operate on the information provided. | PERFORM charp_raise('USERPARAMPERM', 'inconsistent str') |
| MAILFAIL | email address | Email processing failed. | PERFORM charp_raise('MAILFAIL', '[email protected]'); |
| DATADUP | Modification to the database would have resulted in undesired duplicate information. | PERFORM charp_raise('DATADUP'); | |
| NOTFOUND | Requested information not found. | PERFORM charp_raise('-NOTFOUND'); -- Data not found, don't log the occurrence. | |
| EXIT | code | The routine ended abruptly, the code specifies the situation. | PERFORM charp_raise('-EXIT', 0); -- Success! |
Here are a few examples of remote procedures with different behaviors:
- A regular remote procedure for administrators that returns the usernames and account_ids of all the users in the system:
CREATE OR REPLACE FUNCTION rp_admin_get_users(_uid charp_user_id)
RETURNS TABLE(username varchar, account_id integer) AS
$BODY$
BEGIN
PERFORM 1 FROM account_admin AS aa WHERE aa.account_id = _uid;
IF NOT FOUND THEN charp_raise('USERPERM'); END IF
RETURN QUERY SELECT a.username, a.account_id FROM account AS a;
END
$BODY$
LANGUAGE plpgsql STABLE;
ALTER FUNCTION rp_admin_get_users(_uid charp_user_id) OWNER TO :conf_user;
COMMENT ON FUNCTION rp_admin_get_users(_uid charp_user_id) IS 'Return all the users on the system.';
The first step in this function is to check that the user really is an administrator. Here, we assume that there is a table which holds a primary foreign key with the IDs of those users who are administrators (this table is not provided by CHARP). Since we only care about the number of resulting rows, we call the checking query using PERFORM ("1" is a placeholder for the required column spec). NOT FOUND will be true if the last query returned no rows, and this would mean that the user is not an administrator, so we raise the corresponding exception, which interrupts the execution for that case. Finally we return the result of the query that brings the desired information.
- An anonymous remote procedure that returns the states or territories of a given country (public information which does not require clearance).
CREATE OR REPLACE FUNCTION rp_anon_get_country_states(_country_id integer) RETURNS TABLE(name varchar, state_id integer) AS $BODY$ BEGIN RETURN QUERY SELECT s.state_name, s.state_id FROM cat_states AS s WHERE s.country_id = _country_id; END $BODY$ LANGUAGE plpgsql IMMUTABLE; ALTER FUNCTION rp_anon_get_country_states(_country_id integer) OWNER TO :conf_user; COMMENT ON FUNCTION rp_anon_get_country_states(_country_id integer) IS 'State names and IDs for a given country ID.';
- A raw data function that returns the contents of the PNG image file of the avatar of the user. Avatar images are assumed to be stored in /home/myforum/avatars/.
CREATE OR REPLACE FUNCTION rp_file_get_avatar(_uid charp_user_id) RETURNS TABLE(mimetype text, filename text) AS $BODY$ BEGIN mimetype := 'image/png'; SELECT '/home/myforum/avatars/' || avatar_file INTO filename FROM account_forums WHERE account_id = _uid; RETURN NEXT; END $BODY$ LANGUAGE plpgsql STABLE; ALTER FUNCTION rp_file_get_avatar(_uid charp_user_id) OWNER TO :conf_user; COMMENT ON FUNCTION rp_file_get_avatar(_uid charp_user_id) IS 'Get the png file for the avatar of a forum user.';