andrewgodwin/whynot.sql

## whynot.sql
-- This presents an example of rendering templates in the database.
-- DO NOT DO THIS! It's a terrible idea, and just example for my talk
-- which you can see at https://speakerdeck.com/andrewgodwin/dubious-database-design

CREATE EXTENSION plpythonu;

-- Table to store template HTML by name
CREATE TABLE templates (
    "name" text PRIMARY KEY,
    "template" text
);

-- Table for basic page data (just use col names in template)
CREATE TABLE pages (
    "id" text PRIMARY KEY,
    "title" text,
    "introduction" text,
    "body" text,
    "created" timestamp with time zone DEFAULT NOW()
);

-- Make a template
INSERT INTO templates
    ("name", "template")
VALUES (
    'test-template',
    '<html><head>
        <title>{{ title }}</title>
    </head>
    <body>
        <h1>{{ title }}</h1>
        <p>{{ introduction }}</p>
        {{ body }}
    </body></html>'
);

-- Make a test page
INSERT INTO pages
    ("id", "title", "introduction", "body")
VALUES (
    'index',
    'Hi DjangoCon!',
    'NEVER DO THIS',
    'I mean it.'
);

-- The evil within - the render function
CREATE FUNCTION render (template_name text, id text)
    RETURNS text
AS $$
    # You'll need jinja2 in your system pythonpath
    import jinja2
    # Fetch the page data as a dict
    data = plpy.execute("""
        SELECT *
        FROM pages
        WHERE id = '%s'
    """ % id, 1)[0]
    # Fetch the template as a string
    template_string = plpy.execute("""
        SELECT template
        FROM templates
        WHERE name = '%s'
    """ % template_name, 1)[0]["template"]
    # Render!
    return jinja2.Environment().from_string(template_string).render(**data)
$$ LANGUAGE plpythonu;

-- Select rendered HTML
SELECT render('test-template', 'index');
	-- This presents an example of rendering templates in the database.
	-- DO NOT DO THIS! It's a terrible idea, and just example for my talk
	-- which you can see at https://speakerdeck.com/andrewgodwin/dubious-database-design

	CREATE EXTENSION plpythonu;

	-- Table to store template HTML by name
	CREATE TABLE templates (
	"name" text PRIMARY KEY,
	"template" text
	);

	-- Table for basic page data (just use col names in template)
	CREATE TABLE pages (
	"id" text PRIMARY KEY,
	"title" text,
	"introduction" text,
	"body" text,
	"created" timestamp with time zone DEFAULT NOW()
	);

	-- Make a template
	INSERT INTO templates
	("name", "template")
	VALUES (
	'test-template',
	'<html><head>
	<title>{{ title }}</title>
	</head>
	<body>
	<h1>{{ title }}</h1>
	<p>{{ introduction }}</p>
	{{ body }}
	</body></html>'
	);

	-- Make a test page
	INSERT INTO pages
	("id", "title", "introduction", "body")
	VALUES (
	'index',
	'Hi DjangoCon!',
	'NEVER DO THIS',
	'I mean it.'
	);

	-- The evil within - the render function
	CREATE FUNCTION render (template_name text, id text)
	RETURNS text
	AS $$
	# You'll need jinja2 in your system pythonpath
	import jinja2
	# Fetch the page data as a dict
	data = plpy.execute("""
	SELECT *
	FROM pages
	WHERE id = '%s'
	""" % id, 1)[0]
	# Fetch the template as a string
	template_string = plpy.execute("""
	SELECT template
	FROM templates
	WHERE name = '%s'
	""" % template_name, 1)[0]["template"]
	# Render!
	return jinja2.Environment().from_string(template_string).render(**data)
	$$ LANGUAGE plpythonu;

	-- Select rendered HTML
	SELECT render('test-template', 'index');