Tag vite : Tags... amplifier application bd blog book computing dailymotion debian dev electronic forum friends geek git github javascript jquery linux net nodejs ooc php pomm postgresql python screen shell silex speaker store symfony ubuntu valve vim whitepaper youtube

This article is following Web developers: what can Postgresql do for you? and in case you haven’t read it yet, I would suggest you had a look to understand why I decided coding Pomm (Postgresql Object Model Manager) and its bundle for Symfony2 .

The Not So Simple Blog will be a stupid blog with articles and comments. We do not care about users in a first step, we will see that point after. The scope of the first iteration is to be able to display blog posts, click and display a post and see all comments. We have to be able to add a comment.

Symfony2 installation

If you are experienced with Symfony2 already, you can probably skip this part. Otherwise, I have set up a distribution with Pomm base on the standard distribution of Symfony2. Be sure to clone the Pomm branch of this repo. Once that downloaded, configure you favorite web server and you are ready to go!

Database work

Database structure

We have first to create the user and the database and install the plpgsql language in it.

postgres=# CREATE USER nss_user LOGIN PASSWORD 'nss_password';
CREATE USER
postgres=# CREATE DATABASE nss_db OWNER nss_user;
CREATE DATABASE
postgres=# c nss_db
Switched to database nss_db
nss_db=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE;

We can now connect to our brandt new database using the nss_user and install the tools we will need. A script with useful SQL functions comes with the Pomm API, just source it from your psql client:

nss_db=> i vendors/pomm/sql/pomm_lib.sql
[...]

Let’s go with the database schema. We are in the blog bundle so we will create everything in the blog schema. We need 2 tables: article and comment

nss_db=> CREATE SCHEMA nss_blog;
CREATE SCHEMA
nss_db=> SET search_path TO nss_blog, public;
SET
nss_db=> CREATE TABLE article (
  slug           varchar(255) PRIMARY KEY,
  title          varchar(255) NOT NULL,
  author         varchar(255) NOT NULL,
  content        text NOT NULL,
  created_at     timestamp NOT NULL DEFAULT now(),
  updated_at     timestamp NOT NULL DEFAULT now(),
  published_at   timestamp,
  CONSTRAINT valid_slug CHECK (slug ~ '^[a-z0-9-]+$')
);
CREATE TABLE
nss_db=> CREATE TABLE comment (
  id             serial PRIMARY KEY,
  article_slug   varchar NOT NULL,
  author         varchar(255) NOT NULL,
  email          varchar(255) NOT NULL,
  created_at     timestamp NOT NULL DEFAULT now(),
  message        text NOT NULL,
  CONSTRAINT valid_email CHECK (is_email(email)),
  FOREIGN KEY (article_slug) REFERENCES article (slug) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE
nss_db=> CREATE OR REPLACE function slugify_title() RETURNS TRIGGER LANGUAGE plpgsql AS $$
  BEGIN
    NEW.slug := slugify(NEW.title);

    RETURN NEW;
  END;
$$;
CREATE FUNCTION
nss_db=> CREATE TRIGGER slugify_title_article
  BEFORE INSERT ON article
  FOR EACH ROW
  EXECUTE PROCEDURE slugify_title();
CREATE TRIGGER
nss_db=> CREATE TRIGGER update_updated_at_article
  BEFORE UPDATE ON article
  FOR EACH ROW
  EXECUTE PROCEDURE update_updated_at();
CREATE TRIGGER
nss_db=> CREATE TRIGGER update_updated_at_comment
  BEFORE UPDATE ON comment
  FOR EACH ROW
  EXECUTE PROCEDURE update_updated_at();
CREATE TRIGGER

Oh well, we are done with the database: we have set a slugification and a timestampable behavior on the article table and a timestampable behavior on the comment table. All the tables, sequences, triggers and indexes belong to the nss_blog schema and use some functions defined in the public schema.

In order to version your database schema, I would strongly recommend to create a backup of the structure of this schema and version it with the rest of your project.

$ mkdir -p database/{backups,fixtures}
$ pg_dump nss_db -U nss_user -h localhost --schema-only -n nss_blog -c > database/backups/nss_blog.sql

Fixtures

Using SQL for the fixtures has advantages like using date and time functions to set the _published_at_ field here:

SET search_path TO nss_blog, public;
TRUNCATE TABLE article CASCADE;
INSERT INTO article (slug, title, author, content, published_at) VALUES
('', 'This is my first article: the begining', 'Homer Simpson', 'This blog post has absolutely no interest, this is why nobody wants to comment it.', now() - '3 days'::interval),
('', 'This is a second article', 'Lisa Simpson', 'As I watch tv sometimes I wish I could go and live on the moon.', now() - '1 days'::interval),
('', 'This is an unpublished article', 'Marge Simpson', 'How does this blog work ? Is there anybody here ?', null);

INSERT INTO comment (article_slug, author, email, message) VALUES 
('this-is-a-second-article', 'Bart', 'bart.simpson@gmail.com', 'I can help you if you want'),
('this-is-a-second-article', 'lisa', 'lisa.simpson@gmail.com', 'Ah ah you stupid snail'),
('this-is-a-second-article', 'Ned Flanders', 'ned.flanders@gmail.com', 'Good Lisa ! The moon is one wonder of the nature.');

Initial Symfony2 setup

Bundle creation and base model set up

We can now create our bundle using the Symfony command line:

app/console init:bundle "NssWebsite\BlogBundle" src NssWebsiteBlogBundle

We have to enable it in the application kernel by adding the following line in app/AppKernel.php:

$bundles[] = new NssWebsiteBlogBundleNssWebsiteBlogBundle();

Declare our namespace in the autoaloading (app/autoload.php):

'NssWebsite'       => __DIR__.'/../src'

and configure the routing system. Do not hesitate to remove all the old stuff you might find in app/config/routing.yml:

nss_blog:
    resource: "@NssWebsiteBlogBundle/Controller/DefaultController.php"
    type:     annotation

We can now ask PommBundle to generate the model base files in the cache. We must specify the schema so it can find our tables:

app/console pomm:mapfile:scan --schema=nss_blog

This creates the base map files for our database entities under the app/cache/Pomm/Model/Map directory. Note that the command line has named our 2 classes BaseNssBlogArticleMap and BaseNssBlogCommentMap using pg schema’s name to prefix our classnames.

Everything is set up, let’s go for the code!

First lines of code: the model

In our bundle, create a ‘Model’ directory. It will contain the database entities. We have to create 4 empty classes (2 per table) there: Comment.php, Article.php and CommentMap.php and ArticleMap.php. Comment and Article classes must extend PommBaseObject and the Map classes must extends their relative classe in the cache:

#Article.php
<?php

namespace NssWebsiteBlogBundleModel;

use PommObjectBaseObject;

class Article extends BaseObject
{
}

#ArticleMap.php
<?php

namespace NssWebsiteBlogBundleModel;

use PommModelMapBaseNssBlogArticleMap;

class ArticleMap extends BaseNssBlogArticleMap
{
}

Do the same thing for the Comment class and the model is ready.

First contact

Our first controller.

Our goal is now to display a list of posts on the welcome page. Let’s edit the existing DefaultController:

#src/NssWebsite/BlogBundle/Controller/DefaultController

    /**
     * @extra:Route("/", name="homepage")
     * @extra:Template("NssWebsiteBlogBundle:Default:index.html.twig")
     **/
    public function indexAction()
    {
        $articles = $this->get('pomm')
            ->getConnection()
            ->getMapFor('NssWebsiteBlogBundleModelArticle')
            ->findAll();

        return array('articles' => $articles);
    }

Let’s spend some time on explanations here. We use the SensioFrameworkExtraBundle’s annotations to setup the routing and ease the call of the templating system. Then we call the pomm service requesting for a connection. As we do not name this connection, pomm returns the first it has (and the only one in our case). The getMapFor method returns an instance of the class passed as argument. So here, we call the method findAll on an ArticleMap instance which returns a Collection of the corresponding Article instances. findAll is itself defined in the BaseOjectMap class.

Now, if you try to execute this controller in your brower by GETing the “/” uri on this virtualhost, you will see an error message saying «Fatal error: Class ‘NssBlogArticle’ not found in /var/www/dev/nsswebsite/vendor/pomm/Pomm/Object/BaseObjectMap.php on line 72». There is here something important to understand before going on. Let’s have a look at what have been generated by Pomm is the cache:

<? #app/cache/Pomm/Model/Map/BaseNssBlogArticleMap.php
    //...
    public function initialize()
    {
        $this->object_class =  'NssBlogArticle';
        $this->object_name  =  'nss_blog.article';

        $this->addField('slug', 'StrType');
        // ...

        $this->primary_key = array('slug');
    }

The _object_class_ attribute is used by the BaseObjectMap class to know what is our class’s relative entity. This is why here it complains not finding this class. There are 2 solutions to solve this problem: either we configure the autoloading to know where to find NssBlog prefixed classes or we override the setting in our own ArticleMap class:

#src/NssWebsite/BlogBundle/Model/ArticleMap.php

class ArticleMap extends BaseNssBlogArticleMap
{
    public function initialize()
    {
        parent::initialize();

        $this->object_class = 'NssWebsiteBlogBundleModelArticle';
    }
}

It is really up to you the way you configure it, if you prefer by example having all the model files in one place (and namespace) you might prefer use the autoloading, if you want to keep your model in your bundle’s namespace, you will override the object_class attribute.

<!-- src/NssWebsite/BlogBundle/Resources/views/Default/index.html.twig -->

{% extends "::base.html.twig" %}

{% block body %}
<h1>Welcome to the Not So Simple Blog</h1>

{% if articles.count == 0 %}
<p>There are no articles yet on this blog, please come back later.</p>
{% else %}
<ul>
{% for article in articles %}
  <li><b>{{ article.getTitle }}</b> published by <i>{{ article.getAuthor | lower }}</i> on {{ article.getPublishedAt | date('Y-m-d H:i:s') }}</li>
{% endfor %}
  </ul>
{% endif %}
{% endblock %}

Of course, you will prefer using nice CSS and presentation. I have used the simpleton CSS from Free Css Templates feel free to use your own.

First thoughts

Do we have exactly what we want? Well, not really: we do display an unpublished article, we would like to display an excerpt of the article’s content and the comments count for each article. This means the findAll method we have used is not accurate, we have to define our own finder, this will be a good reason to publish another article on this blog. Take care!