Deploying code is a big part of our job and we’re always looking to increase our efficiency when deploying applications. Recently, we decided that our goal is to package every application as a single file archive that can be easily built and deployed. We want our continuous integration system to spit out a single file per project that can be used to deploy the everything. PHP offers a way to store PHP apps into one single file, a PHP Archive or “phar” file, so we began our experiments with phar archive deployment.

To test deployments of PHP apps in a phar archive, we generated a very basic Yii Framework-based web application for testing: a “yii/” directory with the Yii Framework files and a “webapp/” directory with the web application files (e.g. “index.php” and “protected/”). We also protected the “yii/” directory with an “.htaccess” file and deleted some runtime data to save up space in the phar archive we wanted to build.

We modified our configuration to serve phar files with the PHP module and whitelisted phar files in the Suhosin PHP extension configuration. We generated a testing “index.phar” archive and put it in the DocumentRoot along with a bootstrap “index.php” file with the following content:

<?php
include ‘phar://index.phar/webapp/index.php’;
__HALT_COMPILER();

An error occurred when the application loaded in the browser: realpath() was not able to determine the location of the “protected/runtime/” directory in the web application. This function seems to be having issues when used inside phar archives and there was no point in storing runtime or user data inside of it.  So we needed a real directory outside the phar file for that. We then overrode realpath() in the bootstrap file with the “runkit” PHP extension.

In the overridden function, we expunged the “phar://” and the “index.phar/webapp/” path components and returned the results when the Yii Framework was trying to determine its runtime directory. If a path was beginning with “phar://” we simply returned it, and if none of those conditions were met, we simply returned the value returned by the original realpath() we made a copy of in the bootstrap file. To correctly display css files stored in the phar archive, we also used “mod_rewrite” to redirect requests to “/index.phar/webapp/css/”. We created the “protected/runtime/” and “assets/” directories outside the phar archive in the DocumentRoot, and we protected the newly created “webapp/protected/” directory with an “.htaccess” file.

We also noticed that captcha images were not being displayed because a needed “ttf” font that ships with the Yii Framework was not found at runtime: dirname() was not able to return/determine the whereabouts of the directory inside the phar archive where that font was. We overrode dirname() to extract that file at runtime from the “index.phar” archive into a temporary location, if not already there; the overridden dirname() was coded to return this new path, or the value returned by the original dirname() function in all the other cases.

As you can see, there are a lot of overrides required just to make a simple application work. We’ve stopped our work on phar archive deployment because managing all of these overrides is unworkable. We also have no assurances that the overrides will be appropriate for a more complicated application.

We’re going to try some other experiments to get closer to our goal of a single file deployment for our applications. Our next experiments will be around automation the creation of tarballs with custom code to deploy them appropriately.

Is anyone else using phar archives to package their applications? We’d be curious to know if anyone else has had better luck. Any comments and ideas are welcome!

Share this:

It is a common practice to add fields such as `created’ or `last_updated’ to a database table to keep track of newly created objects or of updated ones from a date and time perspective.

MySQL has very useful date/time functions such as `NOW(),’ and `CURRENT_TIMESTAMP()’ or `CURRENT_TIMESTAMP’ which actually are both synonyms for `NOW(),’ as clearly explained in the MySQL manual available on the MySQL website. It is possible to use such useful functions to automatically populate database fields that hold datetime information from within MySQL itself as an alternative to set those fields from an application.

The MySQL version that was used when writing this post was `5.1.50.’ It does not seem to be possible to apply function calls like CURRENT_TIMESTAMP() on more than one TIMESTAMP field within the same table. For instance, if we execute the following SQL statement:

  -- Example: *wrong* `CREATE TABLE' query
  DROP TABLE IF EXISTS `test`.`tbl_testing`;
  CREATE TABLE `test`.`tbl_testing` (
    `test_id` INT NOT NULL AUTO_INCREMENT ,
    `test_title` VARCHAR(128) NOT NULL ,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ,
    `last_updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP() ,
    PRIMARY KEY (`test_id`)
  );

we will get the following error from MySQL:

  `ERROR 1293 (HY000): Incorrect table definition; there can be
  only one TIMESTAMP column with CURRENT_TIMESTAMP
  in DEFAULT or ON UPDATE clause'.

If we create the two TIMESTAMP fields such as they are both NULL and do not contain any CURRENT_TIMESTAMP() calls:

  [...]
    `created` TIMESTAMP ,
    `last_updated` TIMESTAMP ,
  [...]

and we pass null values for both of them on an INSERT statement, it seems to be widely known that MySQL will automatically populate both fields with the current timestamp, even if we did not specify any value for each field on the CREATE TABLE query. It should also be widely known that, for instance, if we write a query and update for the `test_id’ field only without referencing the timestamp fields in the SQL statement, MySQL will update the timestamp value on the first TIMESTAMP column only. In this case it is possible to invert the order of the two fields so `last_updated’ comes first and gets updated.

But what if we need to refactor our table(s) and we forget about this detail (field order), or if we pass a null value by mistake to the `created’ column by referencing it on an UPDATE statement? In both cases the `created’ field will be always updated with the current timestamp, and in the first case the `last_updated’ field will not hold the right data. In other words, it could be a tad difficult to do some troubleshooting — and it could also be a bit painful to maintain our code when we have many tables. We also want to make sure that the timestamp records in our rows are correct.

A good trade-off in terms of code maintenance and data consistency could be possible by using triggers without worrying about the order of the fields and what we pass as parameters for them in our queries. For instance, we might want to store the creation date for a newly created row and leave the `last_updated’ field blank in order to quickly determine if a row has been updated since its creation. But we can obviously set the same datetime value to both fields if we prefer. On an UPDATE statement, the `created’ field will remain unchanged, and the `last_updated’ column will be holding the last datetime information. The way we will create the trigger will also provide for two aspects: even if we specify either null or not null values for the `created’ and `last_updated’ fields, both those fields will always hold the original creation date and the last updated datetime info:

  -- we will be using triggers for `created' and `last_updated'
  DROP TABLE IF EXISTS `test`.`tbl_testing`;
  CREATE TABLE `test`.`tbl_testing` (
    `test_id` INT NOT NULL AUTO_INCREMENT ,
    `test_title` VARCHAR(128) NOT NULL ,
    `created` TIMESTAMP NULL ,
    `last_updated` TIMESTAMP NULL ,
    PRIMARY KEY (`test_id`)
  );

  USE `test`;

  DELIMITER $$
  -- (re)-creating the "before insert" trigger
  DROP TRIGGER IF EXISTS `tr_testing_bi` $$
  CREATE TRIGGER `tr_testing_bi` BEFORE INSERT ON `tbl_testing`
    FOR EACH ROW BEGIN
      SET NEW.`created` = CURRENT_TIMESTAMP();
      SET NEW.`last_updated` = NULL;
    END;
  $$
  -- (re)-creating the "before update" trigger
  DROP TRIGGER IF EXISTS `tr_testing_bu` $$
  CREATE TRIGGER `tr_testing_bu` BEFORE UPDATE ON `tbl_testing`
    FOR EACH ROW BEGIN
      SET NEW.`last_updated` = CURRENT_TIMESTAMP();
      SET NEW.`created` = OLD.`created`;
    END;
  $$
  DELIMITER ;

We can observe the following:

• we are still using the TIMESTAMP data type for both the `created’ and `last_updated’ fields and their position in the fields list does not matter;
• both the `created’ and `last_updated’ fields do not have any CURRENT_TIMESTAMP() function calls in the CREATE TABLE statement and are set to NULL;
• the `tr_testing_bi’ trigger will set the `created’ field (NEW.`created` data) with a valid timestamp and whatever value we specify on the INSERT query for the `created’ field will be overwritten with the information returned by the CURRENT_TIMESTAMP() MySQL function;
• the `tr_testing_bi’ trigger will also be responsible for setting the `last_updated’ field to `NULL’, as we are just adding a new row;
• the `tr_testing_bu’ trigger will make sure the same logic applies to the `last_updated’ field — the last updated data that will be set to the `last_updated’ field (NEW.`last_updated` data) will be coming from the output of the CURRENT_TIMESTAMP() function as well;
• in the `tr_testing_bu’ trigger code, we also make sure the original value for the `created’ field is preserved — in the event of an erroneous update for that field, we will always set this value to its original one: `SET NEW.created = OLD.created’ will perform this check for us;
• we are using the CURRENT_TIMESTAMP() function on both triggers to create timestamps, but, of course, we can also use its aliases, NOW() or CURRENT_TIMESTAMP, or the UTC_TIMESTAMP() function (or UTC_TIMESTAMP) if we wish to store our timestamp data using the UTC format in our database.

Although we will have a bit more SQL code to jot down because we added two triggers — each for a different event (BEFORE INSERT and BEFORE UPDATE), we should also have a more robust structure and a strict approach that helps prevent manual updates for the timestamp fields.

Share this:

Many of our clients use a combination of communication tools to keep in touch — whether Outlook/Exchange, Apple Mail.app, Entourage, or Google Apps. Combining these tools can create some technical challenges, as technologies from different manufacturers don’t always play nicely together. Today I wanted to share a workaround that I use in my personal calendaring setup: using Google Calendars with Apple’s iCal. This necessitates figuring out how to get Google Calendar ‘delegate calendars’ to sync with iPhone/iPod Touch via iTunes.

Google Calendar is a great tool for managing appointments, accessible from anywhere (check out Colin’s thoughts on Google Apps). It is also very versatile as it offers the ability to import iCal .ics files and MS Outlook CSV calendar snapshots. A delegate calendar is an additional calendar you can add to your existing Google Calendar account. At the present time, it is not possible to sync/view delegate calendars when syncing an iPhone/iPod Touch from iTunes. They will not show up there as it looks like this feature is not supported/implemented at the present time. There is a simple workaround which will allow users to sync delegate calendars from within iTunes. The following steps will guide you through the process:

STEP 1: disable your delegate calendars from iCal

1. Open iCal, then select ‘Preferences’ from the iCal dropdown menu
2. Select your Google CalDAV account and open the ‘Delegation’ tab at the top of the window
3. Uncheck each checkbox corresponding to each delegate calendar; they will disappear from the main calendar view where they were currently shown as delegates

Disable your delegate calendars from iCal

STEP 2: create the necessary account(s) for each delegate calendar

A typical Google Account URL has the following form: https://www.google.com/calendar/dav/USERNAME@gmail.com/user

This should be already present on your existing CalDAV account (the one you expunged the delegates from). Replace ‘USERNAME’ with your Google Account username, then copy and paste the line above into a temporary text document for now.

1. Open your browser and log in into your Google Account, then follow the Calendar link
2. Select ‘Settings’ on the ‘My calendars’ box on the left column of the page
3. Select the delegate calendar you want to keep in sync from the main page’s frame
4. At the bottom of the next page (the with the selected calendar’s details), copy the Calendar ID value (shown as clear text) which can be found in the ‘Calendar Address’ section; the Calendar ID has the following sample structure: qwertyuiopasdfghjkl1234567@group.calendar.google.com
5. Go back to the previous temporary text document where you saved your Google Account URL, paste the Calendar ID value you copied in the previous step and replace the ‘USERNAME@gmail.com’ part as follows (replace the Calendar ID with your correct value):https://www.google.com/calendar/dav/qwertyuiopasdfghjkl1234567@group.calendar.google.com/user
6. Then copy the whole resulting string
7. Go to iCal, select Preferences once again and add a new CalDAV account: please make sure you expand the ‘Server Option’ dropdown and paste the aforementioned string into the ‘Account URL’ field.

Create the necessary account(s) for each delegate calendar

You will need to create a brand new iCal account for each delegate you have set up on Google Calendar. When done, your iCal software will display each delegate(s) as separate calendars, and each one of them will be shown into iTunes and will be available for synchronization in the Device’s Info tab.

Each delegate(s) as separate calendars

Share this: