Author Archives: chris

Using DBIx::Class::Tree::NestedSet

I’ve been struggling to get DBIx::Class::Tree::NestedSet working today because I found the documentation a bit sparse.

The problem is basically that the instructions assume that I’m very familiar with DBIx::Class, and that therefore I don’t need much detail.

After much trial and error I got it to work.

So these notes are aimed at someone who has got the basics of DBIx::Class running, with all the clever schema classes created (either manually or via something like dbicdump).

For this example, the tree is in a table called ‘treetable’, whose schema is defined in App::DB::Schema.  The table needs to have the following columns, in addition to the ones that you need for your application:


  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  root_id  integer,
  lft      integer NOT NULL,
  rgt      integer NOT NULL,
  level    integer NOT NULL,

This line:

__PACKAGE__->load_components(qw( Tree::NestedSet ... ));

needs to go near the bottom of App::DB::Schema.pm, after the ‘DO NOT MODIFY THIS OR ANYTHING ABOVE!‘ line.  Note that the ellipsis (...) needs to be either deleted or replaced with any components that you’re already using.

These lines:

__PACKAGE__->tree_columns({
    root_column  => 'root_id',
    left_column  => 'lft',
    right_column => 'rgt',
    level_column => 'level',
});

go in App::DB::Schema::Result::Treetable.pm, after the ‘DO NOT MODIFY THIS OR ANYTHING ABOVE!‘ line, and with the column names set to match the ones in your table.

That same file, App::DB::Schema::Result::Treetable.pm, is the one that needs

use parent DBIx::Class::Tree::NestedSet;

near the top.

Once that all compiles cleanly, you can start accessing the tree table.

Start by creating the result set in the usual DBIC way:

my $rs = $schema->resultset('Treetable');

where $schema is your database handle.

Then you can start using the tree relationships and methods however you like.  Note that the documentation uses $node to refer to each node in the tree: each such $node is a DBIC object that can be used to access the fields from the corresponding database row, navigate to other nodes (children, siblings, etc.), or create new children or siblings.

For example,

my $first = $rs->first;  # get the first (or only) root node
my $desc_rs = $first->descendants;
while (my $desc = $desc_rs->next) { 
    print '**'x$desc->level, " id=", $desc->id, 
        ", field1=", $desc->name, "\n";
}

I hope that makes sense.

Website version control with Git

Some notes on using git to manage development and production versions of a website on a Linux server, based on Using Git to manage a web site.  There seem to be several web pages with similar ideas out there: I don’t know who wrote it down first.  And also with reference to Version Control with Git by Jon Loeliger.

I’ve adapted those ideas for the way I like to do things:

  • I SSH in to the server, and do the editing there, using vim.
  • I have separate domains for development and production versions of my sites.  For the purposes of these notes, they’re called dev.example.org and www.example.org.  So the development version is also an active real-world website: my nginx configuration makes it only visible to me.
  • The document roots are /var/www/website and /var/www/website-dev respectively.
  • The ‘bare’ production git repository can be anywhere on the server.  I’ll put it at /var/www/website.git.  It’s a git convention to use the .git extension for bare repositories.

The steps for setting it up are as follows.  I’ll leave the setting of suitable permissions and use of sudo as an exercise for the reader.

  1. Put some web pages in /var/www/website-dev.
  2. mkdir /var/www/website
    cd /var/www/website-dev
    git init
    git add <all the appropriate files and directories>
    
  3. Create a .gitignore file, and add everything that shouldn’t be transferred to production, such as configuration files that specify the development database and debug settings. For a WordPress site, that probably includes most of the WordPress stuff.
  4. git commit -a -m "a message"
    mkdir /var/www/website.git
    cd /var/www/website.git
    git --bare init
  5. Create /var/www/website.git/hooks/post-receive containing:

     

    #!/bin/bash
    GIT_WORK_TREE=/var/www/website git checkout -f
  6. In the following, I’ve used ‘live’ as an alias for the production environment; you could use ‘prod’ or whatever you fancy.
  7. chmod +x /var/www/website.git/hoots/post-receive
    cd /var/www/website-dev
    git remote add live file:///var/www/website.git
    git push live +master:refs/heads/master
    git push --set-upstream live master
    git push live
  8. And, as if by magic, the files from the master branch of /var/www/website-dev are now in /var/www/website.
  9. Then whenever you’ve got new code ready to into production, all that’s required is:

     

    git push live

Self-signed multiple-domain SSL certificates

I’ve finally worked out how to create self-signed SSL certificates for multiple domain names with openssl. 

These notes relate to Debian GNU/Linux, but the principles will apply to other operating systems.

The first step to make the process easier and repeatable in the future is to copy the default configuration file from /etc/ssl/openssl.cnf to a working directory where you can adapt it

Let’s assume that you’ve copied /etc/ssl/openssl.cnf to ~/project-openssl.cnf.  Edit the new file and set the various default values to the ones that you need — that’s better than having to respond to openssl’s prompts every time you run it.

For real non-self-signed certificates, you would generate a certificate signing request (.csr) file, ready for a certificate authority to sign it for you.  In that case, you need to follow the instructions at http://wiki.cacert.org/FAQ/subjectAltName.

But for a self-signed certificate, the subjectAltName has to go in a different place.  Make sure you’ve got this line present and un-commented in the [req] section of the config file:

[req]
...
x509_extensions = v3_ca

and then this goes at the end of the [v3_ca] section:

[v3_ca]
...
subjectAltName = @alt_names
[alt_names]
DNS.1 = example.com
DNS.2 = www.example.com
DNS.3 = example.co.uk
DNS.4 = www.example.co.uk

There is (apparently) a limit to the number (or total length) of the alternate names, but I didn’t reach it with 11 domain names.

It’s also possible to add IP addresses to the alt_names section like this:

IP.1 = 192.168.1.1
IP.2 = 192.168.69.14

Then to create the key and self-signed certificate, run commands similar to these:

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout project.key -out project.crt -config ~/project-openssl.cnf
cp project.crt /etc/ssl/localcerts/
mv project.key /etc/ssl/private/

Note that I move (rather than copy) the key to the private directory to avoid leaving a copy of it lying around unprotected.

You can check that the certificate contains all the domains that you added by running this:

openssl x509 -in project.crt -text -noout | less

Alternative approach

I haven’t tried this, but according to http://apetec.com/support/GenerateSAN-CSR.htm it’s also possible to create a CSR and then self-sign it like this:

openssl x509 -req -days 3650 -in project.csr -signkey project.key
 -out project.crt v3_req -extfile project-openssl.cnf

References: