CMS written entirely in PL/pgSQL

In one of my previous jobs, I heard that it would be inspiring, to write a CMS with all logic stored and executed on database side. Every request would be performed in stored procedures, theirs final result would be HTML page sent back to the user.

I will present, how to do it with PotgreSQL, Apache, Linux and a bit of bash scripting. The script is a cheat because I promised to write everything in stored procedures, but as you will see, it's just a gateway to the database, there isn't any logic.

We will start from the configuration of the Apache server, as a root, go to /etc/apache2 and create web-sql file in sites-available directory. Its content should be as below.

NameVirtualHost 127.0.0.1:8888
<VirtualHost 127.0.0.1:8888>
        ServerAdmin webmaster@localhost

        DocumentRoot /opt/websql
        <Directory />
                Options FollowSymLinks
                AllowOverride None
        </Directory>
        <Directory /opt/websql>
                Options Indexes FollowSymLinks MultiViews ExecCGI
                AllowOverride None
                Order allow,deny
                allow from all
                # This directive allows us to have apache2's default start page
                # in /apache2-default/, but still have / go to the right place
                #RedirectMatch ^/$ /apache2-default/
        </Directory>

        ErrorLog /var/log/apache2/error.log

        # Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn

        CustomLog /var/log/apache2/access.log combined
        ServerSignature On

</VirtualHost>

In /etc/apache2/sites-enabled make link to the above file. After typing ls -la in command line it should look like this:

lrwxrwxrwx 1 root root   26 2008-05-26 00:00 web-sql -> ../sites-available/web-sql

Now we have virtual server that runs on 127.0.0.1 address, on 8888 port and keeps its files in /opt/websql.

In /opt make websql directory, inside create main.cgi file with below content.

#!/bin/sh
echo "select index('$QUERY_STRING')" | psql  -U userszybkosci -t --no-align -d testszybkosci

The script executes index() procedure with a string that was given by the user as an input parameter. We don't want to display the number of returned rows, so we are using -t parameter, spaces automatically inserted by psql are disabled by using --no-align. Mentioned options ensures that the output of the script is equivalent to the output of the index() procedure.

We need to create a user, a database and enable PL/pgSQL language on the database:

robert@robert855:~$ sudo bash
Password:
root@robert855:~# su postgres 
postgres@robert855:/home/robert$ createuser userszybkosci
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
postgres@robert855:/home/robert$ createdb -O userszybkosci testszybkosci
CREATE DATABASE
postgres@robert855:/home/robert$ createlang plpgsql testszybkosci

Now we can move to the merit and start to create stored procedures and tables. They will be used to provide the functionality of our simple CMS:

--
-- Name: atrybut; Type: TABLE; Schema: public; Owner: userszybkosci; Tablespace: 
--

CREATE TABLE atrybut (
    tag integer,
    nazwa text,
    wartosc text
);


ALTER TABLE public.atrybut OWNER TO userszybkosci;

--
-- Name: str_seq; Type: SEQUENCE; Schema: public; Owner: userszybkosci
--

CREATE SEQUENCE str_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.str_seq OWNER TO userszybkosci;

--
-- Name: strona; Type: TABLE; Schema: public; Owner: userszybkosci; Tablespace: 
--

CREATE TABLE strona (
    id integer NOT NULL,
    tresc text,
    nazwa_pliku text,
    tytul text
);


ALTER TABLE public.strona OWNER TO userszybkosci;

--
-- Name: tag; Type: TABLE; Schema: public; Owner: userszybkosci; Tablespace: 
--

CREATE TABLE tag (
    id integer NOT NULL,
    nazwa text
);


ALTER TABLE public.tag OWNER TO userszybkosci;

--
-- Name: tag_seq; Type: SEQUENCE; Schema: public; Owner: userszybkosci
--

CREATE SEQUENCE tag_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.tag_seq OWNER TO userszybkosci;

--
-- Name: strona_pkey; Type: CONSTRAINT; Schema: public; Owner: userszybkosci; Tablespace: 
--

ALTER TABLE ONLY strona
    ADD CONSTRAINT strona_pkey PRIMARY KEY (id);


--
-- Name: tag_pkey; Type: CONSTRAINT; Schema: public; Owner: userszybkosci; Tablespace: 
--

ALTER TABLE ONLY tag
    ADD CONSTRAINT tag_pkey PRIMARY KEY (id);

-- tutaj trafiaja zapytania, ona generuje tresc, taki
-- engine, mozna by dorobic obsluge akcji etc..
CREATE OR REPLACE FUNCTION index(text) RETURNS text AS $$
DECLARE
    nazwa_pliku ALIAS FOR $1;
    DECLARE output TEXT DEFAULT E'Content-Type: text/html\n\n';
BEGIN
    -- zadanie wyslania arkusza styli
    IF nazwa_pliku='style' THEN
        output = output || add_styles();
        RETURN output;
    END IF;
    output = output || '<html><link rel="stylesheet" href="main.cgi?style"/><body>' || 
             page_content(nazwa_pliku) ||
             '</html></body>';
    RETURN output;
END;
$$ LANGUAGE 'plpgsql';


-- zraca style, gotowe do wsadzenia w zanaczniki <style> albo do 
-- wpisanie w plik CSS
CREATE OR REPLACE FUNCTION add_styles() RETURNS text AS $$
DECLARE
    DECLARE record RECORD;
    DECLARE subrecord RECORD;
    DECLARE output TEXT DEFAULT '';
BEGIN
    -- petla po wszystkich tagach w arkuszy styli
    FOR record IN SELECT * FROM tag LOOP
        output:= output || record.nazwa || E' {\n'; -- poczatkowa klamerka
        -- petla po wszytkich atrybutach dla tagu
        FOR subrecord IN SELECT nazwa, wartosc FROM atrybut WHERE tag=record.id LOOP
            output:= output || subrecord.nazwa || ':' || subrecord.wartosc ||  E';\n';
        END LOOP;
        output:= output || E'}\n'; -- koncowa klamerka
    END LOOP;
    -- skoro jestesmy tuta to w zmiennej output mamy gotowy plik css 
    RETURN output;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION page_content(TEXT) RETURNS text AS $$
DECLARE
    DECLARE output TEXT DEFAULT '';
    klucz ALIAS FOR $1;
    DECLARE r RECORD;
BEGIN
    SELECT INTO r *  FROM strona WHERE nazwa_pliku=klucz;
    IF NOT FOUND THEN
        return 'Podana strona nie istnieje.';
    END IF;
    output = '<h1>tytul: ' || r.tytul || '</h1><hr/>tresc: ' || r.tresc;
    RETURN output;
END;
$$ LANGUAGE plpgsql;

Above data is just a dump of my database, it can be fetched by executing in command line:

plpgsql -U user_name -d databse_name -f name_of_file_with_above_dump

Now, when the database is ready, we can reset the Apache server (/etc/init.d/apache2 restart) and add some articles by using psql. Results are visible on screen:

The sources can be obtained by cloning git@github.com:RobertGawron/snippets.git (CMSInPureSQL directory).