In this section we'll go over creating the views and stored functions that our REST Server service will rely on.
Our REST service will be a thin wrapper around a pgsql function that accepts film queries and returns results in XML.
Step 1: Download the Pagila 0.10.0 database from
http://pgfoundry.org/frs/?group_id=1000150&release_id=570
and load the Pagila database using the following commands:
Note:For windows users - psql is usually located at "C:\Program Files\PostgreSQL\8.3\bin\psql"
psql -h localhost -p 5433 -U postgres -c "CREATE DATABASE pagila ENCODING 'UTF8'"
psql -h localhost -p 5433 -U postgres -c "CREATE ROLE pagila_app LOGIN PASSWORD 'pg@123'"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-schema.sql"
psql -h localhost -p 5433 -U postgres -d pagila -f "pagila-data.sql"
We are creating a view to make querying the data easier, but we don't need to give our pagila_app account rights to the view since all access will be thru our stored function. The view is mostly there to provide a namespace for the XML output.
CREATE OR REPLACE VIEW vwfilms AS
SELECT film.film_id AS fid, film.title, film.description, category.name AS category,
film.rental_rate AS price, film.length, film.rating, film.fulltext
FROM category
LEFT JOIN film_category
ON category.category_id = film_category.category_id
LEFT JOIN film
ON film_category.film_id = film.film_id
Now we shall write a stored function that takes a search criteria and returns an XML result. Note that we are designating the function is accessed with SECURITY DEFINER which means the executer of the function has all rights to items used within the function as long as the creator of the function has rights. Keep in mind we didn't really think long and hard about how to do this efficiently so our implementation of converting to Tsearch format is probably highly inefficient and is a brain dead implementation of converting a simple search phrase into suitable Tsearch format. Feel free to comment as to a better way of doing this or bash us for our stupid implementation. We'll try hard not to be offended.
This is shown below:
CREATE OR REPLACE FUNCTION fnget_film_search_results(arg_search character varying,
arg_num_results integer, arg_start_at integer)
RETURNS xml AS
$BODY$
--#GIVEN A users search criteria - convert to TSearch Suitable format
--#Return results as XML to be consumed by REST client;
DECLARE
var_sql text;
var_count integer;
var_count_xml xml;
var_tsearch_query text;
BEGIN
var_tsearch_query := REPLACE(REPLACE(REPLACE(array_to_string(ARRAY(SELECT lower(foo)
FROM
regexp_split_to_table(arg_search, E'\\s+') As foo
WHERE foo SIMILAR TO E'\\w+'), '&'), '|and', '&'), '|or', '|') , 'not&', '!');
RAISE NOTICE '"%"', var_tsearch_query;
SELECT count(fid) INTO var_count FROM vwfilms WHERE fulltext @@ to_tsquery(var_tsearch_query);
var_count_xml := '<?xml version="1.1"?><resultsummary><count>' || CAST(var_count As varchar(20))
|| '</count></resultsummary>';
var_sql := 'SELECT fid, title, description, category, price, length, rating
FROM vwfilms WHERE fulltext @@ to_tsquery(' || quote_literal(var_tsearch_query) || ')';
var_sql := var_sql || ' LIMIT ' || CAST(arg_num_results As varchar(20))
|| ' OFFSET ' || CAST(arg_start_at as varchar(20));
RETURN '<results>' || xmlconcat(var_count_xml, query_to_xml(var_sql, false, false, 'vwfilms'))
|| '</results>';
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100;
GRANT EXECUTE ON FUNCTION fnget_film_search_results(character varying, integer, integer) TO pagila_app;
To test out our function we do this:
SELECT fnget_film_search_results('not epic but has dogs',10,1);
SELECT fnget_film_search_results('not epic but has dogs',10,20);
In the next issue we will cover consuming this via a .NET REST Service. We will show both C# and VB.NET implementations which are compatible with Microsoft.NET ASP.NET 2.0 as well as Mono.NET 1.2.6 (C# and Mono Basic).