Our Blog

PostgreSQL Functional Indexes

By Szymon Lipiński
June 10, 2013

PostgreSQL has got the great feature named “functional indexes”. A normal index just stores sorted values of some field. It is great for searching, as the values are already sorted.

You can create an index with a simple query like:

CREATE INDEX i_test ON test (i);

It will store all values of column i from table test. This index can be used with a query like:

SELECT * FROM test WHERE i < 100 ORDER BY i;

Functional Indexes

There is also something I like most. Index can store all values you want, they don’t need to be values from the table. You can use values calculated from the table columns. They will be sorted, so searching with those indexes will be pretty fast.

Creating such index is simple:

CREATE INDEX i_test_lower_i ON test (lower(i));

The main rule is: this index can be used if you have the same function call in your query, something like:

SELECT * FROM test WHERE lower(i) = 'aaa';

Example

Let’s check something more complicated. My test table looks like:

CREATE TABLE test(t timestamp);

I filled this table with sample data. We need some bigger number of rows:

INSERT INTO test(t) SELECT generate_series(now() - '1 year'::interval, now(), '1 …

Creating custom button graphics in Android

By Zed Jensen
June 7, 2013

In the Android timesheet app I’m working on, I have a scrollable layout of RadioButtons for the user to pick how much time they’ve spent on a project (see my earlier blog post about it), and for that I use custom button graphics to make it look nice. So, I’m going to show you how to do that with 9-patch PNGs and selector XML.

First, what’s a 9-patch PNG? A 9-patch is a special PNG image where you specify regions that can be stretched to make room for text. Android will automatically resize a 9-patch to best fit whatever contents you give it. The tool you need to create a 9-patch image is included in the Android SDK Tools, so download that if you haven’t already.

More information about 9-patch images can be found here.

Okay! I’ve got custom button graphics (72x72 for HDPI screens), drawn in the Gimp and saved in my project’s res/drawable-hdpi/ folder as button_selected.png and button_unselected.png:

To convert it to a 9-patch, browse to the tools/ directory of the Android SDK and run draw9patch. This will open a window with a graphical editor on the left, and a button preview on the right. The editor window is for specifying which parts of the image will be stretched. The …

JSConf US — Day 2

By Greg Davidson
June 5, 2013

Choose Your Own Adventure

For day two of JSConf, the organizers decided to try something new. Rather than a day of scheduled talks, attendees could choose from a variety of activities. There was kayaking, golf, segway tours, scavenger hunts or you could just hang out at the pool if you like. There was also the opportunity to hack on NodeBots or Node Copters. While the outdoor stuff sounded awesome, I opted to hack on and play with the nodecopters.

Node Copter

With the help of nodejitsu, Chris Williams was able to bring 50 Parrot AR Drone quadcopters for teams to play with and hack on with JavaScript. Felix Geisendorfer and a contingent of volunteers showed us how to fly the quadcopters (with either an iOS or Android device) and how they could be controlled with JavaScript code. Felix is the author of the ar-drone node.js module which makes this possible. With a laptop connected to the quadcopter, the following code is all you need to have it take off, rotate a little bit, perform a flip and then land:

var arDrone = require('ar-drone');
var client = arDrone.createClient();

client.takeoff();

client
  .after(5000, function() {
    this.clockwise(0.5);
  })
  .after(3000, …

Window functions in action

By Josh Tolley
June 5, 2013

Image by Wikimedia user Ardfern

Yesterday I ran on to a nice practical application of a number of slightly unusual SQL features, in particular, window functions. PostgreSQL has had window functions for quite a while now (since version 8.4, in fact, the oldest version still officially supported), but even though they’re part of the SQL standard, window functions aren’t necessarily a feature people use every day. As a bonus, I also threw in some common table expressions (also known as CTEs, also a SQL standard feature), to help break up what could have been a more confusing, complex query.

A client of ours noticed a problem in some new code they were working on. It was possible for users to submit duplicate orders to the system in quick succession, by double-clicking or something similar. This was fixed in the code easily enough, but we needed to clean up the duplicate orders in the database. Which meant we had to find them. We defined a group of duplicates as all orders involving the same line items, with one of a set of possible status codes, created in an interval of less than five minutes by the same user.

This discussion of the time interval between two different records should …

DevOps engineer job opening (remote)

By Jon Jensen
June 4, 2013

This position has been filled. See our active job listings here.

End Point continues to grow! We are looking for a full-time, salaried DevOps engineer to work on projects with our internal server hosting team and our external clients. If you like to figure out and solve problems, if you take responsibility for getting a job done well without intensive oversight, please read on.

What is in it for you?

• Work from your home office
• Flexible full-time work hours
• Health insurance benefit
• 401(k) retirement savings plan
• Annual bonus opportunity
• Ability to move without being tied to your job location
• Collaborate with a team that knows their stuff

What you will be doing:

• Remotely set up and maintain Linux servers (mostly RHEL/CentOS, Debian, and Ubuntu), with custom software written mostly in Ruby, Python, Perl, and PHP
• Audit and improve security, backups, reliability, monitoring (with Nagios etc.)
• Support developer use of major language ecosystems: Perl’s CPAN, Python PyPI (pip/easy_install), Ruby gems, PHP PEAR/PECL, etc.
• Automate provisioning with Ansible, Chef, Puppet, etc.
• Use open source tools and contribute back as opportunity arises
• Use your desktop platform of choice: Linux, Mac OS X, Windows

What you will need:

• Professional experience with …

JSConf US 2013 — Day One

By Greg Davidson
June 4, 2013

I attended JSConf in Amelia Island, FL last week. As you can see, the venue was pretty spectacular and the somewhat remote location lent itself very well to the vision set by the conference organizers. Many developers myself included, often find the line between work and play blurring because there is much work to be done, many new open source projects to check out, constant advancements in browser technology, programming languages, you name it. Keeping up with it all is fun but can be challenging at times. While the talks were amazing, the focus and ethos of JSConf as I experienced it was more about people and building on the incredible community we have. I highly recommend attending meetups or conferences in your field if possible.

Without further ado, I’ve written about some of the talks I attended. Enjoy!

Day One

Experimenting With WebRTC

Remy Sharp presented the first talk of the day about his experience building a Google Chrome Experiment with WebRTC and the peer to peer communication API. The game (headshots), was built to work specifically on Chrome for Android Beta. Because WebRTC is so young, the libraries supporting it (Peer.js, easyRTC, WebRTC.io, SimpleWebRTC) are …

CSS Conf 2013 — When Bootstrap Attacks!

By Greg Davidson
June 3, 2013

I attended the inaugural CSS Conf last week at Amelia Island, Florida. The conference was organized by Nicole Sullivan, Brett Stimmerman, Jonathan Snook, and Paul Irish and put on with help from a host of volunteers. The talks were presented in a single track style on a wide range of CSS-related topics; there was something interesting for everyone working in this space. I really enjoyed the conference, learned lots and had great discussions with a variety of people hacking on interesting things with CSS. In the coming days I will be blogging about some of the talks I attended and sharing what I learned, so stay tuned!

When Bootstrap Attacks

Pamela Fox had the opening slot and spoke about the experiences and challenges she faced when upgrading Bootstrap to V2 in a large web app (Coursera). What she initially thought would be a quick project turned into a month-long “BOOTSTRAPV2ATHON”. Bootstrap styles were used throughout the project in dozens of PHP, CSS and JavaScript files. The fact that Bootstrap uses generic CSS class names like “alert”, “btn”, error etc made it very difficult to grep through the codebase for them. The Bootstrap classes were also used as hooks by the project’s …

PostgreSQL as NoSQL with Data Validation

By Szymon Lipiński
June 3, 2013

PostgreSQL is a relational database with many great features. There are also many so called NoSQL databases, some of them, like CouchDB, are document databases. However the document in CouchDB is automatically enhanced with a “_id” field, if it is not present. When you want to get this one document, you can use this “_id” field—​it behaves exactly like the primary key from relational databases. PostgreSQL stores data in tables’ rows while CouchDB stores data as JSON documents. On one hand CouchDB seems like a great solution, as you can have all the different data from different PostgreSQL tables in just one JSON document. This flexibility comes with a cost of no constraints on the data structure, which can be really appealing at the first moment and really frustrating when you have a huge database and some of the documents contain bad values or there are missing some fields.

PostgreSQL 9.3 comes with great features which can turn it into a NoSQL database, with full transaction support, storing JSON documents with constraints on the fields data.

Simple Example

I will show how to do it using a very simple example of a table with products. Each product has a name, description, some id …