How to import tickets into Assembla

api, assembla, ideas 3 Comments »

I use Assembla for my projects, and I found that export/import tickets feature works tricky — it works based on a combination of JSON and CSV format. It seems the guys in Assembla are on a way to proprietary formats 😉 The only usage of it is only for moving tickets from one Assembla space to another.

So this post is about how to use what they offer to add lots of tickets that you have listed in your text file. I am lazy — instead of adding 90+ tickets manually, I’d better create a tool to do it for me.


You have tickets titles and descriptions as text, and you want to add these tickets to Assembla.

My solution

Let’s investigate what format should our data be of so that Assembla could digest it. Go to a space → TicketsSettings → scroll down and find Export & Import → click “Export your tickets” links and open the dump file in a text editor. Though it’s indicated that “Tickets imported and exported in JSON format.“, it’s not. It’s a mix.

The dump file contains blocks that represent spaces, milestones, tickets, comments, custom fields values and tickets associations. Every such block contains 2 parts: list of fields and bigger part — actual data of this fields.

What we need is tickets part.

This is a format definition part:

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority","status","description","created_on","updated_at","milestone_id","component_id","notification_list","completed_date","working_hours","is_story","from_support"]

And this is an example of ticket data:

tickets, ["40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","Property owner profile - create new","3","3","","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

So, the solution is to create a list of tickets data by the format given. Here is how I did that.

1. I have Excel spreadsheet with two columns — the ticket title and description.

2. Export it as CSV, and open it having it like this:

Home page: Update design,Set numbers after locations
Photos: hide step 2,Show only Step 1 until you select files and then show step 2 to upload files.

3. Use text replace to put every value in quotes (hint: use a line ending symbol as a replacement — even Microsoft Word can do that)

"Home page: Update design","Set numbers after locations"
"Photos upload: hide step 2","Show Step 2 only if files are selected."

(One way round is to load the CSV data into database by PHPMyAdmin and export it back as CSV — it will be quoted 🙂 )

4. Add Assembla header to the top of this file

5. Move ‘summary‘ and ‘description‘ columns go first, so it was

tickets:fields, ["id","number","reporter_id","assigned_to_id","space_id","summary","priority",...

and it becomes

tickets:fields, ["summary","description","id","number","reporter_id","assigned_to_id","space_id","priority",...

(We need all the columns because it seems that Assembla’s import engine is not smart enough to deal with missing fields).

6. Edit our tickets data lines (text replace again) so that it looked like Assembla tickets data, i.e. add ‘tickets, [‘ at the beginning, dummy values for the rest of the fields and the closing square bracket. Don’t forget that we moved ‘summary‘ and ‘description‘ columns to go first!. So we have:

tickets, ["Home page: Update design","Set numbers after locations","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]
tickets, ["Photos upload: hide step 2","Show Step 2 only if files are selected.","40999","1","dNltOqvXKr3RN3e","FoGbar3zTtab7rAJ","b3OWYeJe5aVNr","3","3","2009-06-16 07:23:35","2009-07-05 10:43:58","94111","12",null,"2009-07-05 10:43:58",null,"0","0"]

By the way, I prefer to clear the values of some columns like ‘id‘, ‘number‘ (ticket number) and so on — so that Assembla assigned it itself.

OK, it seems your file is ready to be fed to Assembla. I advise you to create an empty free space and try on it first if don’t want to mess up your current space.

If the import engine has some parsing problems with your file (missing quote for example) you will get a notice. If you get an Application Error, some columns or their values are missing — check your file.

One more tip — your tickets will be added as ‘No milestone‘. If you want your new tickets to be added into a new milestone, paste a milestone defenition in the beginning of your file and replace milestone ID in the tickets data by it’s ID:

milestones:fields, ["id","due_date","title","user_id","created_at","created_by","space_id","description","is_completed","completed_date","from_basecamp","basecamp_milestone_id","updated_at","updated_by"]
milestones, ["1111","2009-09-09","Big shot of development","cv2gFo","2009-08-25 13:12:09","cv2gFo","bktPVqwKmr3OWYeJe5aVNr","Work hard to finish all that was discussed.","0",null,"0",null,"2009-08-25 13:12:09","cv2gFo"]

Sharing work between agents

db, development, ideas, php Comments Off on Sharing work between agents

There are situations when you need to separate processing of big amount of data between several “agents”, e.g.:

  • you have a long list of websites which must be checked for being alive (404 error check) by your web-clawlers;
  • a queue of photos to be resized or videos to be converted;
  • articles that your editors must review;
  • catalogue of blog feeds that your system must import posts from;
  • etc.

The idea to do this is simple:

  1. Give a small piece of big work to an agent.
  2. Mark this piece as given to him (so that none other starts to do the same job) and remember the time stamp when the job was given or when the job becomes obsolete (this agent is dead, let’s give this job to someone else).
  3. If work is done — go to step #1.
  4. After some period of time (1 hour) check all the time stamps, and if some agents didn’t cope with the job, mark the jobs as free so that others could start to work on it.

The problem is between steps #1 and #2 — while you gave a job to Agent 1 and going to mark it as given to him, what if Agent 2 is given by the same job? If you have many Agents, this  can happen at real. This situations is called concurrent read/write.

To overcome this a lock can be used.

In this article I wil explain, how to use locks in Zend project with MySQL database.

First of all, MySQL documentation tells that SELECT .. FOR UPDATE can be used for that purpose. First step is to select records by that statement, and second step is to mark them as locked. Requirements are to use InnoDB storage and to frame these two statements in a transaction.

Happily, Zend_Db_Table_Select has a special method forUpdate() that implements SELECT .. FOR UPDATE statement. Zend_Db can cope with transactions as well. Let’s try it!

To lock a record, we need two fields:

  1. one to remember ID of agent that is processing this record (let’s call this column ‘locked_by‘)
  2. one another to know the time when the lock becomes obsolete (let’s call this column ‘expires_at‘)

I wrote a  class that inherits from Zend_Db_Table and helps to get records with locking them.


class Koodix_Db_Table_Lockable extends Zend_Db_Table
    protected $_lockedByField = 'locked_by';
    protected $_expiresAtField = 'expires_at';
    protected $_TTL = '1 HOUR'; //time to live for lock
    public function fetchLocked( Zend_Db_Table_Select $select, 
        $lockerID ) {
        $db = $this->getAdapter();
        $column = $db->quoteIdentifier( $this->_lockedByField );
             ->where("$column=? OR $column IS NULL", $lockerID);

        $data = $this->fetchAll($select);
        if( empty($data) ) return null;
        $expiresAt = new Zend_Db_Expr('DATE_ADD( NOW(), 
            INTERVAL ' . $this->_TTL . ')');
        if( sizeof($this->_primary) > 1 ) {
            foreach( $data as $item ) {
                $item->{$this->_lockedByField} = $lockerID;
                $item->{$this->_expiresAtField} = $expiresAt;
        else {
            $arrIds = array();
            foreach( $data as $item ) {
                $arrIds[] = $item->id;
                    $this->_lockedByField => $lockerID,
                    $this->_expiresAtField => $expiresAt,
                $db->quoteIdentifier(current($this->_primary)) . 
                    ' IN ("'.implode('","', $arrIds).'")'
        return $data;

    public function releaseLocks( ) {
        $column = $db->quoteIdentifier( $this->_expiresAtField );
        return $this->update(
                $this->_lockedByField => null,
                $this->_expiresAtField => null,
            "$column <= NOW()"

If the table has a composite primary key (containing more than one column), the ActiveRecord approach is used, so the save() method for every record is called, that’s simple (drawback — multiple update queries). Otherwise, if it is a deep-seated table with one ID column as a primary key, then the IDs are collected in a list and all records are updated by a single statement with IN in where clause (which is much faster).

TTL (‘Time to Live‘) — period of time when lock is allowed. In my application the default is one hour. Format of TTL can be seen in MySQL documentation.

And now how to use it.

Let’s imagine you have several editors that divide the big articles list and review them. My model class has a method fetchForUser() that returns no more than 5 articles for current user (by given user ID).

This is an Article table model, inherited from the class above. Usually such classes are located at

class ArticleTable extends Koodix_Db_Table_Lockable
    protected $_name = 'article';
    public function fetchForUser( $userId, $count=5 ) {
        $select = $this->select()
            ->where('reviewed = 0')
            ->order('expires_at DESC')
            ->order('date_imported DESC')
            ->limit( $count );
        return $this->fetchLocked($select, $userId);

Note: if the editor refreses the page, the expres_at fields is refreshed by current time as well.

As for step four of our algorithm (releasing all obsolete locks) — create an action in your backend controller, call your table model releaseLocks() method in it and call that action periodically by Cron.

To boost the performance of the lock releasing, create an index on the expires_at column. (Because of this reason I rejected the ‘locked_since‘ column in favor of ‘expires_at‘)

P.S. In my database date/time columns have DATETIME type. If you use INT to store timestamps, convert it to unix time and back.

Several sites on single WordPress installation

development, ideas, php, wordpress 5 Comments »

I have a couple of other WordPress blogs on the same server besides this one. One day I realised that all of them have 3 different WP versions and, as result, different admin areas which is not handy. I decided to make them use the same WordPress installation.

Ok, first of all, I deleted wp-admin and wp-includes folders and created new ones as symbolic links. Though the frontend worked well, I couldn’t log in into admin area, because the browser was redirected to that blog which was the base for all the rest for unknown reason.

The investigation shown, that admin area of WordPress is a separate sub-application, thing in itself, and in my case it resolves the absolute path to its source as the path to the base blog. I wanted each blog to use its own folder because there are config file and uploads folder.

It took me some time to find a solution. It requires two steps.

First, I added this line to the top of the .htaccess to make any PHP request to the blog (the blog front-end and the admin area scripts) call the same script before thier start:

#fix for several sites on the same WP installation
php_value auto_prepend_file "/var/www/site_doc_root/prepend.php"

In this code /var/www/ is the root folder for all my sites, and the site_doc_root is the document root of the current site (folder where all its files are located).

OK, the 2nd step — the contents of the prepend.php script. It is easy — it just must define an absolute path constant which is used all around the WordPress:

define('ABSPATH', dirname(__FILE__).'/');

OK, after that I decided not to use one of the blogs as source for others, but download a fresh copy of WordPress and make it a source of the symbolic links for all my blogs. This helps to update them.

Then I deleted wp-admin and wp-includes folders and some wp-files and recreated them as symlinks. Attention to wp-config.php — don’t delete it, keep it unique for every site!

To make this task easier, I created file, pasted the contents I show below, run this command

chmod 755

then I copied it in every site folder and launched there for every site:

ln -s /var/www/wordpress/wp-admin wp-admin
ln -s /var/www/wordpress/wp-includes wp-includes

ln -s /var/www/wordpress/wp-app.php wp-app.php
ln -s /var/www/wordpress/wp-atom.php wp-atom.php
ln -s /var/www/wordpress/wp-blog-header.php wp-blog-header.php
ln -s /var/www/wordpress/wp-comments-post.php wp-comments-post.php
ln -s /var/www/wordpress/wp-commentsrss2.php wp-commentsrss2.php
ln -s /var/www/wordpress/wp-config-sample.php wp-config-sample.php
ln -s /var/www/wordpress/wp-cron.php wp-cron.php
ln -s /var/www/wordpress/wp-feed.php wp-feed.php
ln -s /var/www/wordpress/wp-links-opml.php wp-links-opml.php
ln -s /var/www/wordpress/wp-load.php wp-load.php
ln -s /var/www/wordpress/wp-login.php wp-login.php
ln -s /var/www/wordpress/wp-mail.php wp-mail.php
ln -s /var/www/wordpress/wp-pass.php wp-pass.php
ln -s /var/www/wordpress/wp-rdf.php wp-rdf.php
ln -s /var/www/wordpress/wp-register.php wp-register.php
ln -s /var/www/wordpress/wp-rss.php wp-rss.php
ln -s /var/www/wordpress/wp-rss2.php wp-rss2.php
ln -s /var/www/wordpress/wp-settings.php wp-settings.php
ln -s /var/www/wordpress/wp-trackback.php wp-trackback.php
ln -s /var/www/wordpress/xmlrpc.php xmlrpc.php

That’s not all 😉

I decided to update my WordPress installation every one or two months.

To do that, in the /var/www/ folder (where all my sites reside) let’s create an update script with the following contents:

wget --timestamping
unzip -o

This will download a fresh copy of the wordpress if it’s changed (though wordpress team doesn’t show the file Last-Modified header, I think one day they will) and unzip it to /var/www/wordpress/ folder which is the source for our symlinks.

Yes, you got it right — launching this script is all I need to update all my blogs.

Let’s make it periodic:

crontab -e

and then add this line to run the update process automatically every 1st day of every month at 9 AM:

0 9 1 * * /var/www/ > mail -s "Wordpress updated"

P.S. Of course, SVN checkout can be used for that purpose 🙂

RSS feed of error log

development, ideas, php Comments Off on RSS feed of error log

Your PHP application logs every error to error log. Do you want to keep track of them in your favourite Feed Reader? Then follow up reading this article!

First of all, you must tell to your application to log errors to a log file. Two parameters must be set: what to log and where to save it. First can be done by error_reporting setting, second — by setting error_log PHP value:

error_reporting( E_ALL & ~E_NOTICE ); //all except notices
ini_set( 'error_log', 'temp/error.log' );

Put it in the beginning of your application, for example, at the top of index.php file.

By the way, you can write something custom in the error log by calling error_log function:


OK, now, how to make a feed from your error log.

You can do this in a few ways.

First, let’s do it manually.

Manual solution

1. Remove ini_set setting described above. Then create .htaccess file in the top folder of your application and place this line in it:

php_value error_log temp/error.log

It will tell to all scripts of your application to take this setting into consideration without manual calling of ini_set function.

2. Create feed.php file. Place this line inside it:

$contents = file_get_contents( ini_get( 'error_log' ) );
echo nl2br( $contents );

It’s dirty for now, but enough as a check.

3. Make sure that your error log file is not empty. Now you can access your feed.php file via browser and check that it’s showing you the contents of your error log.

4. Now you should create an RSS feed from contents of the file. You can rely on your framework or use a custom solution, e.g. download EasyRSS class and fill the contents of the feed with data from the error log (you would need to think about regexp to parse date and error text from it).

After that you could feed your feed reader with the feed address ;]

Ready-made solution

Download a ready PHP class that will do all it for you — RSS Feed. Yes, it’s simple. You can even protect your feed with a password.


  • on the production server change your error_reporting value, for example, to log fatal errors only.
  • log errors in the catch() part of the exception handling mechanism:
    try{ ... }
    catch(Exception $e) {
      error_log( $e->getMessage() );

Using for marketing purposes

ideas, marketing, php, php classes 2 Comments »

Once upon a time I took a look at my Google Analytics account and found out that is 5th source medium for two my blogs (this one and my russian blog).

I have 8 classes submitted. Calculation shows that 7% of my blogs visitors are my classes downloaders and viewers for all the time my classes and blogs exist, and 11% – for last week.

Take into account, that

  • I get these people to me without any move – I have just placed the links once.
  • in-bound links improve Google PageRank
  • count of links is limited only by your imagination ;]

To check that all my classes have all needed links in “Related links” section, I wrote a PHP script that adds a link if it’s not present on your class page. It was written fast so there not so much error checks; to use it, just

  1. type your login/pass,
  2. list IDs of your classes and
  3. define the list of links to be placed in “Related links” section of every class page.

Script supports more than one login/pass pair. If such link is already present, it will be skipped. CURL is required for your PHP server.

You can download it and use for free.

Do you need a handwriting generating service?

ideas 6 Comments »

Now, my reader, I need your feedback: do you think it’s a good idea to create a handwriting generation service?

The idea is you come to a site, type some text in a form and as result download a PDF file which being printed looks exactly as if you wrote by a black gel ink pen.

I’m sure I can do this since I was working on such stand-alone tool several years ago. It would be fun for me to reimplement it for on-line use, but is there any point in such service?

What do you think?

P.S. It’s not a font looking like a hand-writing. You won’t recognize that it was generated+printed if an ink printer is used. As example:

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in