Installing mdbtools, MariaDB, Nginx, and PHP on FreeBSD 9.1

This was originally a 2-part series, but has since been edited to a single post as part 2 was never fully completed.

Sometimes building a new server is easier than upgrading an old one, with the added bonus of staying up to date with current install procedures. Here’s the procedure I followed to build a new FreeBSD 9.1 server recently. I’ll be installing everything on the bare server (no jails).

FWIW, this server configuration is now being called a “LEMP” server, for Linux, Nginx (spelled “EngineX” for the “E”), MySQL (or MariaDB), and PHP (or as in my case, either PHP or Python).

The major applications being installed and their versions are:

  • FreeBSD 9.1-RELEASE
  • mdbtools v0.7 (project head from GitHub)
  • MariaDB 5.5.31
  • Nginx 1.4.2
  • PHP 5.5.1
  • phpMyAdmin 4.0.5

Create a virtual machine

You can build a bare-metal server, but a Virtual Machine (vm) can be more convenient to work with, and a dump from the vm can be easily restored on a bare-metal server if needed. The virtualizing environment I use is VirtualBox. Start by create a basic virtual machine for BSD (FreeBSD), with 256 MB memory, a 20G IDE primary master drive, and a CD/DVD drive IDE secondary master.

You may also need to configure the network interface for your situation. Consider how the vm will connect to the internet, and how you will connect to it. My default configuration is to bridge my laptop and the vm network interfaces. This gives me access to the vm from the host and from any other devices on my LAN, but requires a DHCP server on your LAN, and port 22 routed (not blocked).

If you don’t have internet access (e.g. if you are in someone else’s conference room), or if you do but port 22 is blocked (e.g. you’re in a certain popular fast-food restaurant with free WiFi), you will need to use a NAT or Host Only network connection in VirtualBox. If you don’t, the IP address Windows choses for itself will typically not be in the same subnet as the IP address VirtualBox choses for the client, and the two will not be able to communicate.

If you use the NAT connection, you will need to forward the following ports in order to communicate with the vm from the host:

ssh               host port 2222        client port 22
HTTP              host port 8880        client port 80
MySQL TCP         host port 3336        client port 3306
MySQL UDP         host port 3336        client port 3306
ftp               host port 2221        client port 21

Install FreeBSD OS

Install FreeBSD base system. Perform a standard install using FreeBSD-9.1-RELEASE-i386-dvd1.iso (or -bootonly.iso), with the following configuration:

hostname: firefly.scc.local
root password: secret
daemons to start at boot: sshd
user: dale (group wheel)

After installing the base system and rebooting, login as root and update the FreeBSD OS:

# freebsd-update fetch
# freebsd-update install

If freebsd-update reports that /usr/src/crypto/ssl/s3_cbc.c is missing, create the directory path (e.g. “# mkidir -p /usr/src/crypto/ssl/”) and fetch/extract again.

Update the ports tree:

# portsnap fetch
# portsnap extract

Edit /etc/hosts to specify a FQDN (fully qualified domain name) for the server:

::1                     localhost firefly.scc.local               localhost firefly.scc.local
#               firefly.scc.local # default VBOX NAT IP address

Install portmaster:

# cd /usr/ports/ports-mgmt/portmaster/
# make install clean

If desired, install and run portaudit to monitor port security notices (you may want to omit this if you won’t be updating the system):

# cd /usr/ports/ports-mgmt/portaudit/
# make install clean
# portaudit -Fda

Edit /etc/ssh/sshd_config to allow remote ssh login by root and user dale, and restart sshd. The base system versions of OpenSSL and OpenSSH will be used (I trust the FreeBSD security committee, and not replace them with updated versions from the ports tree just to be up to date).

root is being allowed remote ssh access for managing databases remotely using MySQL Workbench. If this isn’t needed, don’t give root remote ssh access to avoid a potential security risk should the vm be deployed to the public one day.

# vi /etc/ssh/sshd_config
# add following
AllowUsers root dale
PermitRootLogin yes
# /etc/rc.d/sshd restart

Copy your ssh public key to ~/.ssh/authorized_keys (e.g. using WinSCP).

If desired (e.g. if this server will be in continuous use), you may want to define a mail alias for the root user, so that local system mail gets forwarded to a real system administrator.

# vi /etc/mail/aliases
add following alias:

Install utility applications

Install some basic utility apps that usually come in handy eventually:

# cd /usr/ports/archivers/p7zip/
# make install clean
# cd /usr/ports/ftp/curl/
# make install clean
# cd /usr/ports/textproc/flip
# make install clean
# cd /usr/usr/ports/devel/git
# make install clean
# cd /usr/ports/www/lynx
# make install clean
# cd /usr/ports/ftp/wget
# make install clean
# cd /usr/ports/archivers/unzip   # might already be installed
# make install clean

Install mdbtools

mdbtools is a suite of utilities for working with data from an MS Jet database on a Unix system. First, install the GNU build toolchain needed to compile mdbtools.

# cd /usr/ports/devel/libtool   # may already be installed
# make install clean

# cd /usr/ports/devel/automake
# make install clean

# cd /usr/ports/devel/autoconf    # may already be installed
# make install clean

# cd /usr/ports/textproc/flex/
# make install clean

# cd /usr/ports/devel/bison/    # may already be installed
# make install clean

# cd /usr/ports/textproc/txt2man/
# make install clean

# cd /usr/ports/devel/glib20  # undocumented dependency
# make install clean

# rehash

Next, clone the mdbtools Github repo locally:

> mkdir ~/src/
> cd ~/src/
> git clone

And finally, build and install mdbtools:

> cd ~/src/mdbtools/
> autoreconf -i -f
> ./configure
> gmake
> su - 
# gmake install

Add the installed mdbtools man pages to manpath (the install uses the Linux-typical /usr/local/share/man/man1/) by creating /usr/local/etc/man.d/mdbtools.conf and rebuilding the whatis database:

# vi /usr/local/etc/man.d/mdbtools.conf
MANPATH /usr/local/share/man
# /etc/periodic/weekly/320.whatis
# exit
> apropos mdb

Install Web App Stack

My goal is a simple common stack using MariaDB and Nginx for hosting PHP and Python-based web applications with MySQL back-ends.

Using MariaDB will mitigate risk of MySQL falling out of favor as Oracle continues to orient MySQL to its own needs, with minimal to no impact on applications and use. However, I expect to be using PostgreSQL more in the future, in particular as OpenERP uses PostgreSQL, but also because I seem to see more projects using it every day.

Nginx will provide improved performance on older and less capable hardware, and is becomming (has become?) the new standard for web servers, with its asynchronous event-driven approach to requst handling (instead of using more threads), and a reportedly simpler configuration (we’ll see about that…).

Install MariaDB Database Server

Install MariaDB:

# cd /usr/ports/databases/mariadb55-server/   # includes client
# make config ; make install clean
# rehash

Edit rc.conf to start MariaDB at boot:

# vi /etc/rc.conf
# add mysql_enable

Manually start MariaDB:

# service mysql-server start

Setup grant tables:

# cd /usr/local/   # mysql_install_db assumes its running from here
# mysql_install_db --user=mysql

Configure root password:

> mysqladmin -u root password 'appleton'
> mysqladmin -u root -p -h firefly.scc.local password 'appleton'

Grant root permission to connect remotely:

> mysql -u root -p
mysql> grant all privileges on *.* to 'root'@'%' identified by 'appleton' with grant option;
mysql> exit;

Use the provided my-medium.cnf config file and edit for using InnoDB tables:

The output from “my_print_defaults –help” implies my-medium.cnf should copied to /etc/my.cnf, but I’ll use the MySQL convention I’ve learned until I know different.

# cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
# vi /var/db/mysql/my.cnf
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Restart MariaDB:

# service mysql-server restart

and finally do a basic test to confirm things are basically working:

> mysql -u root -p
MariaDB [(none)]> show databases;
MariaDB [(none)]> use test;
MariaDB [(test)]> exit;

Install Nginx web server

Nginx will interoperate with PHP via FastCGI and PHP-FPM (FastCGI Process Manager), and with Python via FastCGI and via the flup library (py27-flup).

Install Nginx with appropriate options (note I’m not enabling SSL, which I don’t need at the moment, but I may wish later I had included it also):

# cd /usr/ports/www/nginx/
# make config
Use the default configuration options:
IPV6 IPv6 protocol support
HTTP Enable HTTP module 
HTTP_CACHE Enable http_cache module 
HTTP_REWRITE Enable http_rewrite module 
HTTP_STATUS Enable http_stub_status module
WWW Enable html status files

# make install clean

The Nginx config file is:

Edit rc.conf to start Nginx at boot:
# vi /etc/rc.conf

Manually start Nginx:

# service nginx start

Test that Nginx is running by browing to the vm (e.g. http://localhost:8880).

Nginx will be configured later.

Install PHP processor

Install PHP:

# cd /usr/ports/lang/php55/
# make config
enable additional options:
FPM Build FPM version

# make install clean

Edit PHP php.ini configuration file:

# cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini
# vi /usr/local/etc/php.ini
# uncomment session.save_path
session.save_path = "/tmp"
# add default date timezone
date.timezone = "America/Edmonton"

Install a motley assortment of PHP extensions (essentially the current requirements for phpMyAdmin, MediaWiki and the Yii framework).

# cd /usr/ports/lang/php55-extensions
# make config
enable additional extensions to install:
PDO_MYSQL (why PDO if not PDO_MYSQL ??)

# make install clean

Configure PHP-FPM

I will be using the fastCGI process manager PHP-FPM (included with PHP starting with release 5.3.3) with Nginx.

Configure PHP-FPM by editing /usr/local/etc/php-fpm.conf:

# vi /usr/local/etc/php-fpm.conf
make following changes ("-" means delete, "+" means add):

-; events.mechanism = epoll
+events.mechanism = kqueue
-listen =
+listen = /var/run/php-fpm.sock
-;listen.owner = www
-; = www
-;listen.mode = 0666
+listen.owner = www = www
+listen.mode = 0666

Edit rc.conf to start PHP-FPM at boot:

# vi /etc/rc.conf

Manually start PHP-FPM:

# service php-fpm start

When user web applications are installed in Part 2, Nginx will be configured for each PHP application to use PHP-FPM.

Install phpMyAdmin

phpMyAdmin provides convenient management of the MariaDB database server, without requiring any client-side software. First, install phpmyadmin from ports:

# cd /usr/ports/databases/phpmyadmin/
# make config
disable options:
APC PHP APC (animated progress bar) support
# make install clean

It seems that pecl-APC (the APC option in the phpMyAdmin config) can’t be compiled with PHP 5.5 (see FreeBSD forum). I’d rather not downgrade to PHP 5.4, and I suspect I can make do without an “animated progress bar”, so I’m unselecting it for now.

Load pma tables:

# cd /usr/local/www/phpMyAdmin
# mysql -u root -p < ./examples/create_tables.sql

Configure Nginx:

# command
# command

Create the phpmyadmin configuration using the setup wizard (and copy to Access http://hostname/phpmyadmin/setup, specify connection type: socket (instead of tcp) and use suggested names for all tables.

Increase max session before auto logout to 9 hrs (from 3 min):

# vi /usr/local/www/phpMyAdmin/
$cfg['LoginCookieValidity'] = 3600 * 9; // 3600 sec/hr * 9 hrs

Also edit session.gc_maxlifetime in php.ini:

# vi /usr/local/etc/php.ini
; increase max session time for phpMyAdmin. Max session time for phpMyAdmin
; set to 9 hrs in phpMyAdmin ((LoginCookieValidity), which
; requires increasing php garbage collection to greater than 9 hrs
; E.g. 32500 sec = (3600 sec/hr * 9 hrs) + 100 sec
session.gc_maxlifetime = 32500

Configure Nginx:

# command
# command
# command

Install requirements for PHP unit and functional testing (optional)

If you’re going to use the server for PHP unit and functional testing, you will likely want to install xdebug and testing frameworks.

Install php-xdebug:

# cd /usr/ports/devel/php-xdebug
# make install clean

Edit /usr/local/etc/php/extensions.ini and comment loading xdebug as std extension.

Edit /usr/local/etc/php.ini to add loading xdebug as zend_extension.

; load xdebug as zend_extension (loading as std extension commented in php/extensions.ini)
; enable profiling
xdebug.profiler_enable = 1
xdebug.profiler_output_dir = /tmp/profiler
; remote settings

Install pear and pear PHPUnit for unit testing Yii-based projects.

# cd /usr/ports/devel/pear
# make install clean
# pear config-set auto_discover 1
# pear install

Install PHP pear Selenium, which is a dependency for Yii PHPUnit testing, but is also used for functional testing. Install curl and PHP curl extension first if not already installed.

# cd /usr/ports/ftp/curl; make install clean
# cd /usr/ports/ftp/php5-curl; make install clean
# pear install phpunit/PHPUnit_Selenium

And that’s that!

Manipulating CSV-format files on FreeBSD

Conclusion Updated 2013-08-16.

The initial Maestro implementation site uses CSV files for intermediate data exported from legacy systems. I’m using CSV files because they are human readable and provide a point of control between the legacy systems and Maestro. They also provide an opportunity to manipulate and report on the data using simple command-line Unix tools.

Doing some cursory research on command-line CSV-file manipulation, solutions mentioned most often included:

  • csvfix is a command line tool that reportedly does everything you’d want, including order/select columns, split/merge, generating SQL inserts from CSV data and diffing CSV data, and has a permissive license.
  • csvkit is a collection of programs adhering to the Unix philosophy (a program should do one thing and do it well), intended to be to CSV files what grep, sed, cut, sort… are to text. csvkit is licensed under the permissive MIT license.
  • csvprintf is a simple UNIX command line utility for parsing CSV files, similar to the printf utility. cvsprintf is written in C, and is licensed under the permissive Apache License, Version 2.0.
  • csvtool is included with the OCaml CSV library.
  • GNU Recutils is a set of tools and libraries to access human-editable, plain text databases called recfiles, with support for CSV file import and export (the recfile concept seems similar to SQLite, but recutils seems to have better csv support than SQLite – e.g. escaping the field delimiter when it occurs within text, supporting \r\n (carriage return – line feed) within a text string, etc.
  • Perl, AWK and R were also mentioned, but working solutions appeared non-trivial for the uninitiated (sorry Randall).

so let’s see what compiles on FreeBSD 8.3…..


csvfix is available as a Windows binary from the project site with the csvfix source on BitBucket. Compiling (using “make lin” for Unix/Linux) resulted in a g++ option error.

From the csvfix wiki, compiling requires a modern version of g++ with C++2011 support – and reportedly uncompilable on Mac OS X by people who know a lot more about compilers than me, so that’s as far as I’ll be going with csvfix for now.


csvkit is written in Python, and requires mkvirtualenv and pip, which I unfortunately don’t have any experience with (yet). I’ll postpone further csvkit investigation for now.


First, I tried the instructions in the download archive:

> tar -xzf csvprintf-1.0.3.tar.gz
 > ls
 csvprintf-1.0.3 csvprintf-1.0.3.tar.gz
 > cd csvprintf-1.0.3
 > ls
 CHANGES configure csvprintf.h xml2csv
 INSTALL aclocal.m4 csv.xsl scripts svnrev.c
 > ./configure
 checking for a BSD-compatible install... /usr/bin/install -c
 checking whether build environment is sane... yes
 checking for a thread-safe mkdir -p... scripts/install-sh -c -d
 checking for gawk... gawk
 checking for iconv_open in -lc... no
 configure: error: required function iconv_open missing

Making a wild stab in the dark (because I don’t know better), I installed iconv-2.0_4 from the ports tree – and got the same error (you’re probably saying “I could have told you so!”).

Next, although from googling it seemed there was a possibility of GNU Linux autoconf miss-reading my FreeBSD system (and iconv_open capability), I tried updating libiconv to see if that changed anything. Unfortunately, portmaster reported my version of Perl (5.10) was out-dated, which I tried updating to 5.16 (instead of the current 5.18, because I read a May 2013 post saying 5.18 broke a lot of modules).

portmaster -o lang/perl5.16 lang/perl5.10

However, that didn’t end in success because I have apparently installed modules from both CPAN and BSDPan, and BSDPan modules can’t be updated using portmaster. I don’t have time to hunt where these modules came from, and if I even need then anymore! Grrrr.


On the plus side, OCaml CSV is in the FreeBSD ports (ocaml-csv), but on the other hand, it pulls in a *lot* of dependencies I won’t use again (including graphics stuff). I went ahead and compiled the port anyway. It completed without error, but it seems csvtool isn’t included in the FreeBSD port (or at least it didn’t get installed). Grrrr.


I’m rolling my own. I’m following tutorials on the Python CMS module, and adapting them to my simple but unique needs – and I need to learn Python anyway for OpenERP (and to hang out with the cool kids). However, I would like to have a general-purpose go-to CVS utility, and look forward to eventually testing csvkit.

New brakes for the Fusion (only a 55km ride to get them)

I lost rear braking on my Rocky Mountain Fusion last week. Biking pathways wasn’t bad, but I didn’t want to ride off-road with only a front brake, so the next day I biked downtown to The Bike Shop on my way home after work (not only did I buy my Fusion there, they also do great drop-in service so long as the job won’t take too long). Adding the 3 legs up in my head as I pedalled home, I was sure I would be close to 100Km. However, Google Maps calculated my route to be 55km, which I trust because my it was pretty straight, and I’ve found Google knows all about Calgary pathways (just make sure you select Bicycling for the navigation mode).

I really liked this route. On the way downtown, I was up and close with one of Calgary’s oldest industrial areas. After my rear brake pads were changed and brake fluid topped up, I left downtown and rode through one of Calgary’s oldest affluent residential areas. I also noticed an unfortunate casulty of this year’s flooding – a missing pedestrian suspension bridge over the Elbow River. I can sympathize with local users and commuters through the area; loosing the pedestrian bridges over the Bow River at both Mackenzie and Southland Park has meant changes to my own travel routes.

Marker “D” is my home, “B” is work, and “C” is The Bike Shop (I was travelling counter-clockwise).

Compiling mdb-tools on Ubuntu 12.04

I recently replaced Linux Mint 13 with Ubuntu 12.04 LTS as the GNU Linux distribution on my dual-boot laptop. One of the first tasks after basic configuration was to install mdb-tools for Maestro development (used to extract data from a Parts&Vendors MS Jet4 database).

Install build dependencies:

$ sudo apt-get install libtool
$ sudo apt-get install automake
$ sudo apt-get install txt2man
$ sudo apt-get install libglib2.0-dev libdb-dev

Clone the mdb-tools GitHub repo:

$ cd ~/src
$ cd src
$ git clone mdbtools
$ cd mdbtools

Compile mdb-tools, and install executables and man pages

$ ./
$ ./configure
$ make
$ sudo make install

Rebuild ld cache:

$ sudo ldconfig

Man pages are installed for mdb-tools executables:

  • mdb-array
  • mdb-export
  • mdb-header
  • mdb-hexdump
  • mdb-parsecvs
  • mdb-prop
  • mdb-schema
  • mdb-sql
  • mdb-tables
  • mdb-ver

For more information on mdb-tools:

For more information on why you have to run ldconfig after installing mdb-tools:


Calgary Flood

If you follow any mass media news, you’ve probably heard about the flooding in Calgary caused by high water volumes in the Bow and Elbow rivers. The rivers join together in the downtown heart of Calgary, where most businesses were shuttered during the worst of the flooding. However, many residents along the rivers were also significantly impacted with lost homes and property. My family has been exceedingly fortunate, only my son’s and my bicycle routes to work have been affected.

In order to bicycle from our home (on the west side of the Bow) to our work (on the east side), there used to be a choice of five bridges – with commute times ranging from 50 minutes to 2 hours depending on the route and bridge. Today, only one of the bridges remains navigable, but the commute is still less than an hour and a half. The bridges themselves do not appear to have been significantly damaged, but the surrounding landscape and pathways leading to the bridges may be forever altered.

My heading photo now shows the McKenzie Meadows golf course, close to the south end of Calgary,  5 days after the start of the flood. The photo was taken looking west, with the Bow River and Rocky Mountains in the background.

Mayan EDMS

I might have mentioned I like the Django web application framework because it’s Python, has good documentation and a strong community (there are even books you can buy!). I spent the first two weeks of 2012 learning some Python and working through The Definitive Guide to Django. Developing and testing locally was easy, but I abandoned the effort after another two weeks trying to configure a Python web stack on my FreeBSD server and returned to the pervasive AMP stack because of its simplicity.

However, yesterday I became aware of the Mayan EDMS project after being featured in a recent FLOSS Weekly podcast (hosted by Randal Schwartz of Perl book fame). It’s a really cool EDMS written using the Django framework, and might be suitable as the DMS component in Maestro.

It seems Django/Python documentation has matured since my aborted effort last year, so a new attempt may be more successful (I’ve also approached some local Meetup groups for some help this time). If I can deploy Mayan to my production server I might be switching frameworks (and languages) again. I don’t have much to lose though because I haven’t really deployed any significant Maestro code yet – most recent work has involved sorting out data structures and synchronizing data from external systems, with some re-usable shell/cron scripts for import data and throw-away ATK and Yii code (I didn’t even write the Yii code, it was generated CRUD code).

What makes Mayan EDMS great? Here are the features as listed on the Mayan EDMS project website, plus a couple additions of my own:

  • Electronic signature verification
  • Unlimited document versioning with revert
  • Unlimited user defined metadata
  • Automatic OCR of documents (with distributed OCR processing)
  • GPL3 license (although I’d prefer a BSD-type license)
  • No commercial “premium” version (the open source version isn’t a limited-feature teaser!)
  • Django/Python
  • and many more….
    • Dynamic default values for metadata
    • Filesystem integration
    • User defined document unique identifier and checksum algorithms
    • Local file or server side file uploads
    • Batch upload many documents with the same meta-data
    • Previews for a great deal of image formats, including PDF
    • Full text searching
    • Configurable document grouping
    • Permissions and roles support
    • Multi-page document support
    • Multilingual user interface: English, Spanish, Portuguese (Brazil and Portugal) Russian, Italian, Polish, German, French, Bulgarian and Dutch.
    • Duplicated document search
    • Plugable storage backends
    • Color coded tagging

I’ll post detailed installation instructions on FreeBSD as soon as I’ve got it working.

cbdb and TrackStar (and WordPress)

I’ve now spent some time with cbdb, and thought I’d share getting it running, and my takeaway from reviewing TrackStar and cbdb features (after that, it’s time to check in with Larry and see what tricks CMS is up to). First though are some comments on WordPress.


I thought I’d include comments on WordPress, since I’m consolidating my personal content using WordPress.

  • WP has a nice plugin management system, with plugin’s automatically adding themselves into the application’s admin menu structure (or Dashboard).
  • If a WP module uses roles role-based authorization, the roles are managed using the plugin’s menu – or at least that’s how the NextGEN gallery plugin does it. Achievo has a single security profiles system that combines the privileges from each module (i.e. the actions that the role controls access to) onto a single role management page. To be honest, I’ve only ever used the system, but the privileges are grouped by module, and are a combination of basic CRUD actions that can be performed on a business object, with some extras.  My preference is the Achievo approach, with all the permission for a role managed in one place (so far, cbdb’s rbac seems close enough).
  • I like how NextGEN handles image uploading (a choice between a drag and drop interface or an Explorer-style interface fr uploading files, multi-file select in the Explorer-style interface, support for uploading zip image archives, and an upload progress bar). Similar functionality would work for uploading files in Maestro.


  • The views generally show raw data, rather than user-oriented information (e.g. type, signed, grade …), and only the create/update form shows user-oriented data (a dropdown selector for Type and Grade, and radio buttons for Signed, Bagged and Collectable). TrackStar polishes things off a bit better in this area.
  • I like that the menu system for cbdb is created early in the development process compared to TrackStar, but the menu colors don’t work for me. I don’t agree that hiding menus a user isn’t authorized to use is good practice, and believe it actually leads to more confusion when users don’t understand why they don’t have have different menus presented, and prefer a static menu (greying-out menus that the user doesn’t have the authority to use, or showing the menu with data but greying-out the Edit or Save button).
  • The calendar wizard to enter dates looks nice, but it’s missing buttons to move between years (although I suspect it can be configured).


  • User management hasn’t been fleshed out as well as in cbdb, in particular not being able to view a list of users associated with a project.

ATK / Achievo Update

I’ve committed to the Yii framework now for building Maestro (check Maestro posts for more information). I’m not following the rss feed from the Achievo forum anymore, and I’ve removed (sigh…) the Achievo/ATK menu from my website so as to not mislead anyone as to my involvement. However, Achievo’s features and simple user and administrator interface are still unrivaled in an open source app of its type, and Maestro will be strongly influenced by Achievo.

In case the information is still of benefit to anyone, here’s the content from the static Achievo/ATK menu page.

- dale

Achievo / ATK

Achievo is a web-based Business Support Services (BSS) application for organizations, built using the ATK Framework and licensed under the GPL. Achievo was originally developed by iBuildings, but is transitioning to community leadership. Achievo includes a rich set of core modules, including support for employees, projects (phases and activities), timesheets, organizations, contacts, CRM (customers, campaings and contracts), and document management. There are a wide variety of Add-on modules available for additional functionality, and you can add or develop Custom modules for more more specific requirements. Achievo is stable and suitable for production.

The ATK framework is a special purpose PHP framework targeted at business applications. It allows database-type applications to be build quickly and with very small amounts of code. Its focus on business features makes it an excellent framework for HRM, CRM, data management and CMS type applications. The ATK framework was originaly developed by iBuildings, but is transitioning to community leadership. The ATK Framework is stable and suitable for production.

Adapto is a new project led by Ivo Jansch, who conceived Achievo and the ATK framework, to re-implement the concepts proven in the ATK framework to the next level, concentrating on easy to use CRUD functionality for relational databases and other data sources with only a few lines of code, and using the Zend Framework (v2) for caching, view rendering, database connectivity, etc..


Getting TrackStar running on Windows/XAMPP and FreeBSD

I’m learning Yii and worked through getting TrackStar (from Web Application Development with Yii and PHP) running locally on Windows/XAMPP, and then on FreeBSD. The trickiest part was getting the trackstar/.htaccess file to correctly hide index.php.

FWIW, I’ve also started experimenting with cbdb (Comic Book DataBase, from Yii Rapid Application Development Hotshot), and will get to CMS next (from The Yii Book). My selfish goal is to use all the applicable features to bootstrap Maestro development (and yes, I did actually purchase them all!).

I started with a zip download of the full TrackStar app from Jeff Winesett’s trackstar repo on GitHub (using trackstar/ directory in the repo) and a yii-1.1.13 download (instead of using the YiiRoot/ directory in the repo). The code on GitHub includes the book errata (compared to the chapter code in the download bundle). I tested TrackStar locally first using Windows XAMPP 1.8.1 (PHP 5.4.7 and MySQL 5.5.27), then moved it to a FreeBSD server.

1. At first (locally), yiic and phpunit didn’t behave as expected, but it was my fault. I edited trackstar/index.php to use yii-1.1.13, but neglected to also edit trackstar/protected/yiic.php and trackstar/protected/tests/bootstrap.php (and for completeness, trackstar/index-test.php also).

2. I had to change “truncateTABLE” to “delete” in two of the migration scripts in order to “migrate down” (MySQL 5.5+ can’t truncate InnoDB tables with foreign keys).

3. TrackStar now runs, but I can’t login because there are no users in the database. The solution was to manually add “User One” and “User Two” with SQL as per the book.

INSERT INTO tbl_user (email, username, password) VALUES
  ('','User One', MD5('test1')),
  ('','User Two', MD5('test2'));

4. Now I can login as either “User One” or “User Two”, but there are errors creating new projects (and there are new projects being added in tbl_project, which could be a bug…). Running “./yiic rbac” fixes the project creation errors. Now I can create projects, issues and comments, assign users to projects, delete projects, etc. TrackStar Rocks!

5. Next, I copied TrackStar from my development workstation to a FreeBSD server (PHP 5.4.10 and MySQL 5.5.30), modified permissions on directories (for reading, and writing to where necessary by Apache), created a trackstar.conf file, restarted apache and created a database. However, in order to connect to the database I had to change the connection string in main.php and console.php from “host=″ to “host=localhost” to get the migration scripts to run and the main trackstar page to load (I also had to change the database username and password in the connection string, but those are secret ;-) ).

6. Now connecting to the database and with the main page loading, navigating using the menu results in a 404 error “The requested URL /usr/local/www/trackstar/index.php was not found on this server.”

The cause of the final problem turned out to be the .htaccess file provided with TrackStar, which  assumes that trackstar is served from the root of the web server (i.e. is TrackStar), when I want “”. I don’t know why it worked locally on XAMPP with the original .htaccess (but figuring it out is on my ToDo list).

Here is my final Apache trackstar.conf

casper# cat /usr/local/etc/apache22/Includes/trackstar.conf
Alias /trackstar "/usr/local/www/trackstar"

<Directory "/usr/local/www/trackstar">
    Options All
    AllowOverride All
    Order Deny,Allow
    Deny from All
    Allow from All

and .htaccess

casper# cat /usr/local/www/trackstar/.htaccess
# Turning on the rewrite engine is necessary for the following rules and features.
# FollowSymLinks must be enabled for this to work.
<IfModule mod_rewrite.c>
  Options +FollowSymlinks
  RewriteEngine On

# Unless an explicit file or directory exists, redirect all request to Yii
# entry script
<IfModule mod_rewrite.c>
  RewriteCond %{REQUEST_FILENAME} !-f
  RewriteCond %{REQUEST_FILENAME} !-d

  # de facto std recommendation, but assumes/requires that app is root of webserver
  # RewriteRule . index.php

  # Yii forum post "Problem with .htaccess"
  # (1) RewriteRule ^.*$ /index.php
  # (2) RewriteRule ^.*$ /trackstar/index.php

  # seems to be more formal version of Yii forum post (1)
  # RewriteRule ^(.*)$ /index.php/$1 [L]

  # seems to be more formal version of Yii forum post (2)
  # RewriteRule ^(.*)$$1 [L,QSA]

  RewriteRule ^.*$ /trackstar/index.php