Room Full of Mirrors

Oracle, Daylight Saving Time, And Unix Timestamps

2012-02-01T16:53:57Z

If ever there was a combination to make you hate developing web applications against an Oracle database, it’s the one defined in the title of this post. Let’s see:

Unix timestamps are used in a lot of web applications because they’re easy to work with
Oracle doesn’t have any native functions for working with Unix timestamps
You have no way of knowing when a bored government will change the dates that DST starts and ends
You shouldn’t include a timezone in a Unix timestamp

After much searching, and piecing together of different solutions to slightly different problems, I have written the following three functions that should cover all of your Unix timestamp handling needs within an Oracle database:

CREATE OR REPLACE
  FUNCTION unix_time
    RETURN INTEGER
  AS
    ut INTEGER     := 0;
    tz VARCHAR2(8) := '';
  BEGIN
    /**
     * This function is for getting the current number of seconds since
     * 01 January 1970 in UTC, i.e. a Unix timestamp.
     *
     * @author James Sumners
     * @date 01 February 2012
     *
     * @return integer
     */

    -- Get the current timezone abbreviation (stupid DST)
    SELECT
      extract(timezone_abbr FROM CURRENT_TIMESTAMP)
    INTO
      tz
    FROM
      dual;

    -- Get the Unix timestamp
    SELECT
      (new_time(sysdate, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (
      86400)
    INTO
      ut
    FROM
      dual;

    RETURN ut;
END unix_time;

CREATE OR REPLACE
  FUNCTION unix_time_to_date(
      unix_time IN INTEGER,
      local_tz  IN INTEGER DEFAULT 0
    )
    RETURN DATE
  AS
    converted_date DATE;
    tz VARCHAR2(8) := '';
  BEGIN
    /**
     * This function is used to convert a Unix timestamp (UTC) into an Oracle DATE. The converted DATE will
     * be in UTC unless the optional local_tz parameter is provided.
     *
     * @author James Sumners
     * @date 01 February 2012
     *
     * @param unix_time The Unix timestamp to convert
     * @param local_tz 0 = UTC, 1 = Local database time zone
     *
     * @return date
     */
    SELECT
      to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + numtodsinterval(
      unix_time, 'SECOND')
    INTO
      converted_date
    FROM
      dual;

    IF local_tz <> 0 THEN
      -- Get the current timezone abbreviation (stupid DST)
      SELECT
        extract(timezone_abbr FROM CURRENT_TIMESTAMP)
      INTO
        tz
      FROM
        dual;
      -- Convert the GMT time to the current local timezone
      converted_date := new_time(converted_date, 'GMT', tz);
    END IF;

    RETURN converted_date;
END unix_time_to_date;

CREATE OR REPLACE
  FUNCTION unix_time_from_date(
      in_date IN DATE
    )
    RETURN INTEGER
  AS
    ut INTEGER     := 0;
    tz VARCHAR2(8) := '';
  BEGIN
    /**
     * This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC).
     *
     * @author James Sumners
     * @date 01 February 2012
     *
     * @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone.
     *
     * @return integer
     */

    -- Get the local timezone from the passed in date
    SELECT
      extract(timezone_abbr FROM CAST(in_date AS TIMESTAMP
    WITH
      local TIME zone))
    INTO
      tz
    FROM
      dual;

    -- Get the Unix timestamp
    SELECT
      (new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (
      86400)
    INTO
      ut
    FROM
      dual;

    RETURN ut;
END unix_time_from_date;

“Expected identifier, string or number”

2011-12-08T19:03:14Z

Thanks, IE.

I just spent pretty much all day tracking down the cause of this error. Conventional wisdom regarding this error is that you have a snippet of JavaScript like (where the comma trailing “42″ is the error):

var myObj = {
    a: "a string",
    b: 42,
}

I didn’t. But IE 8 was still throwing up this error message and its developer tools were pointing me at a line of HTML that contained nothing more than “<div>”. WTF, right? So after much confusion, and narrowing of code, I found the line IE was really barfing on. Consider the following object:

var myObj = {
    default: "ie blows",
    b: 42
}

Can you guess the problem? IE does not like keywords being used as the name of an object property. If you really want to use a keyword, like the jQuery plugin I’m using does, then write your object with JSON style syntax:

var myObj = {
    "default": "ie blows",
    "b": 42
}

Create A Daemon Out Of Any Program

2011-11-04T17:12:40Z

I find myself having to write a script that does this sort of thing every time I have a need. I finally got fed up enough with that to write a generic script that can be used with any interactive program. The script can be found on my BitBucket site under the Scripts repository; the script is named screen_daemon.sh.

The script, as should be obvious, relies on the screen utility to manage the “daemon.” Screen specifically supports this type of thing with the “-Dm” switch combination. This switch combination starts screen in detached mode but doesn’t “fork a new process” (`man screen`). This means that screen starts whatever program you pass in and quits as soon as that program terminates. Thus, you can safely assume that the process id (pid) of the screen session is the pid of your daemon. On lines 82 – 84 of screen_daemon.sh I provide an example that takes advantage of this fact for programs that don’t write their own pid file.

If you create a daemon using this script, you can add a cronjob that runs your daemon script at regular interval, say every five minutes, to keep the daemon running. If the daemon is already running, the script will simply exit. If the script determines that an instance is not running, or has crashed in some way, it will start a new instance.

~/.ssh/config

2011-09-28T19:59:07Z

~/.ssh/config is a file I have not had occasion to use until today. Evidently not many other people use it very often either, because finding the information I needed was more difficult than it should have been. Thus, this post is meant to make it easier to find.

I am currently hosting a private Minecraft server through redstonehost.com for a few friends. My hosting plan is a VPS specifically designed for easy Minecraft server administration. It being a VPS, I have direct SSH access to the server. The SSH account is pre-configured by RedstoneHost to a specific user that manages your Minecraft server. The password for this user is configured by you via your RedstoneHost user control panel. The password I configured is a complex, auto-generated, password.

Given that my password for the VPS is complex, I would rather use an SSH key file for authentication when I need to login. However, I do not want to use the same key that I use for other systems. This is where the need for the ~/.ssh/config file comes into play.

When you do `ssh james@example.com` the remote system sends a packet back telling your local SSH client what forms of authentication it will accept. If the remote server accepts key files, and you have your key configured with the remote host, it will attempt to validate your identity based on the keys installed in your ~/.ssh/ directory. Except, it doesn’t validate against just any key files in the directory. SSH2 specifically checks agains ~/.ssh/id_rsa and ~/.ssh/id_dsa before giving up on key file authentication and moving on to the next method. Thus, if you want to use a key file ~/.ssh/example_com_dsa you would need to do `ssh -i ~/.ssh/example_com_dsa james@example.com`. This is definitely more typing than anyone wants to do just to connect to a host.

To solve this problem, you can add the following to your ~/.ssh/config file:

Host example.com
    User james
    IdentityFile %d/.ssh/example_com_dsa

Now every time you do `ssh example.com` you will be connecting to example.com as if you had done `ssh -i ~/.ssh/example_com_dsa james@example.com` instead.

For more information on what options are available to you in the config file, read through the manpage. For some more examples of how the config file can be helpful to you, look through this ALE thread (Atlanta Linux Enthusiasts).

Sonar 2011 Remix Competition And My Re-emerging Hobby

2011-07-13T19:35:20Z

I have entered the Sonar 2011 remix competition by Loopmasters with a track I’m calling Sonacid. It’s got a Wink style acid house vibe. This is my first public release since getting started with Ableton Live four months ago.

Which brings me to the second part of this post’s title. Before I truly committed myself to completing my degree, I spent a lot of time with music. After I completed my degree, I dabbled with music occasionally. I created some rather decent arrangements with Garage Band, but nothing I would consider totally original and worthy of getting published. Everything has changed in the past four months.

Ableton Live has brought back the desire in me to create original music. Music has once again become my primary hobby, and I have worked in Live almost every single day over the past four months. I have created several new tracks, a couple of which are fully complete, and I continue to learn and have fun with Live. But since Live isn’t some amatuer hour free tracker, I have opted not to release any of this material for free. My plan is to release my work via the UltraPhonix Recordings (UPR) label. In fact, my first release was scheduled to be sometime this month, but extenuating circumstances has delayed that plan.

So, until then, please enjoy a taste of my upcoming work with Sonacid. I am not making it available for download, hence it only being available for streaming on SoundCloud, just yet. After the competition results are posted, I might enable downloading of the track. But I might also wait and see if UPR would like to release it.