We Solve Problems

Welcome to Ninth Avenue. Here you will find articles, howtos and bug fixes for server-side webapp development with Java EE, JSF, Unix and CSS.

How To Export Redmine Issues Using SQL

By , 18 June 2015

I stopped using Redmine a few years ago and left all that data in the database just sitting there rotting away. Some of my notes were pretty good so I decided to see if I could get all those issues out of Redmine without reinstalling the whole damn stack again.

Here is the query I came up with to export all the issues and issue comments into a readable text format. Once exported you can load it up in a text editor that supports code folding like VIM.

The query is for postgresql and has some non-standard SQL functions so it is not portable, but you might be able to find some replacements for those functions for your database system.

$ psql -A -F '' -c "
  select '{{{ ' || project_id || '/' || id || ' ' ||  subject || E'\n\n',
         replace(regexp_replace(description, '\s+$', ''), '}}}', '} } }') || E'\n',
         (select array_to_string(array_agg('Comment posted on ' || created_on || E'\n\n' ||
                 replace(regexp_replace(notes, '\s+$', ''), '}}}', '} } }')), E'\n\n') from
                 (select * from journals where notes <> '' and journalized_type='Issue' and
                           journalized_id=issues.id order by created_on) as ordered_comments), E'\n}}}'
         from issues order by project_id desc, created_on desc" -Upostgres -hlocalhost redmine > issues.txt

$ vim issues.txt
:set foldmethod=marker

It's surprising how useful simple text format is. No database, no network, no ruby, no browser. Just good ol' text.

No comments yet, be the first to comment!

Simple Android File Chooser

By , 3 June 2015

Surprisingly, the Android API doesn't include a file chooser. I'm not sure why that is, I guess the developers don't want to make the assumption that the device has a user filesystem available. Anyway, it's not my job to speculate on what the Android developers talk about over lunch. I just need a file chooser.

It seems like there are a few options out there but I wanted something uber simple and ultra light. It's just for users to select an import file.

My solution is a single Java class utilising regular android Dialog and ListViews. It just wires up the event handlers to read from the disk and refresh the list. You provide a FileSelectedListener implementation to handle the file selection event. Menial stuff that you don't want to bother implementing yourself. It can be used like this:

new FileChooser(activity).setFileListener(new FileSelectedListener() {
    @Override public void fileSelected(final File file) {
        // do something with the file

Here is the FileChooser class which you can cut and paste into your project. Public domain code so do what you want with it.



2 comments, post a comment!

How to Build an iOS Toolchain for Linux (Debian 7)

By , 4 April 2015

After jailbreaking an iPhone I discovered it was a device that had more behind it than just a dumbed down user interface. I had unlocked a unix operating system with a complete package manager (cydia/apt) and development tools to boot. This was pretty cool, I could even compile programs for iPhone on an iPhone.

Cool, yes. But perhaps not very practical for developing complex applications. For that you need a mac right?

Well... no. You don't.

Look at the tools under the hood and you'll see it is a mostly open source stack powered by clang, llvm and a custom linker for the darwin/mach kernel. This article will show you how to build an iOS toolchain for Linux Debian 7 (Wheezy). It is basically a fork of the instructions on the iOS Clang Toolchain installation page but has been updated for Debian 7 and includes a few little patches and tweaks here and there.


5 comments, post a comment!

How to Install ASSP 2.x on Debian 7 Wheezy

By , 18 March 2015

ASSP is an SMTP proxy than runs infront of your mailserver to filter out all the spam email. Despite being implemented as a monolothic, indecipherable perl script, it is actually very effective and runs with very little intervention once you have it configured correctly.


Get the code from assp.sourceforge.net and extract it to /opt/assp-2.4.3 or wherever you would like to deploy it.

Install dependencies

The README has some details about downloading dependencies from CPAN, however my preference is to use the Debian packages wherever possible, so I figured out which packages those were and came up with this list:


3 comments, post a comment!

JPA CascadeType.REMOVE vs Hibernate @OnDelete

By , 13 June 2014

Somehow database models and ORM always end up being more difficult than you expect. Here is a common source of confusion between JPA cascade operations and database cascade operations. Basically they do the opposite thing. For example:

public class House {

    Object door;

If you use CascadeType.REMOVE then deleting the house will also delete the door (using an extra SQL statement).

    Object door;

If you use @OnDelete then deleting the door will also delete the house (using an ON DELETE CASCADE database foreign key).

    @OnDelete(action = OnDeleteAction.CASCADE)
    Object door;

JPA has not standardized the ON DELETE and ON UPDATE foreign key actions possibly because they are SQL-specific and JPA is supposed to be storage-agnostic. I think this is unfortunate - what I'm looking for is ON DELETE SET NULL which would mean that when I delete the door, House.door gets set to null automatically. It's a fairly common requirement and is implemented in OpenJPA like this:

    Object door;

For the moment it looks like I'll have to stick to OpenJPA. Not sure why this isn't an option in Hibernate.

No comments yet, be the first to comment!

OrmLite DAO Factories And Connections Made Easy

By , 7 June 2014

OrmLite comes with some classes to help manage connections, but unfortunately they are poorly named and just plain confusing. For example, OpenHelperManager manages connections - and see if you can guess how that relates to OrmLiteSqliteOpenHelper or the DaoManager. This short blog gives you some simple code to make your API more intuitive and readable.

Connection management and singleton objects are never a simple combination. That's why we do things like abstraction and encapsulation. What I set out to do was have an API that looked like this:

    CategoryDAO dao = CategoryDAO.connect(context);
    List categories = dao.getCategories();

To get there it helps to know what is going on under the hood. So let's reverse engineer some of those oddly named OrmLite classes:

  • OrmLiteSqliteOpenHelper (Database Definition). Defines how to connect to the database and create and update the tables. This is where the real connection object lives. 
  • OpenHelperManager (Connection Management). Keeps count of the number of times the database (connection) has been requested and released. When the count goes to zero it releases the database resources and when it goes back up it recreates them.
  • DaoManager (Singleton Management). Ensures that only one instance of a DAO is created for a given connection. Most apps will only connect to one database, so this effectively implements a singleton pattern. It is necessary because creating DAOs is expensive in Android due to the reflection involved.

Now, we can tie these objects together to implement the API we'd like. Here's an example:

public class CategoryDAO extends BaseDaoImpl<Category, Integer> {
    public CategoryDAO(ConnectionSource source) throws SQLException {
        super(source, Category.class);
     * Static instantiation methods. The database connection is accessed via
     * the OpenHelperManager which keeps a count of the number of objects
     * using the connection. Thus every call to connect() must be matched by
     * a call to release() once the session is done. 
    public static CategoryDAO connect(Context context) {
        return with(OpenHelperManager.getHelper(context, Database.class)
    public static CategoryDAO with(ConnectionSource connection) {
        try {
            return (CategoryDAO) DaoManager.createDao(connection, Category.class);
        } catch (SQLException e) {
            throw new RuntimeException(e);

     * Releasing the DAO flags the connection manager that the DAO is no
     * longer using the connection. When the connection count is zero, the
     * connection manager will close the database.
    public void release() {

Now our target code above will run, OrmLite will manage the singletons and the OpenHelperThingy will reuse connections across the DAOs.  The alternative factory method CategoryDAO.with(connection) is useful for unit tests, or for using one DAO inside another like this:

    public void update(Category category) {

Here, we just reuse the same connection as the host DAO. For a unit test, you can set up the connection to the test database manually (or use Robolectric to mock the Android context).

Let me know if you think there is anything wrong with this pattern in the comments below. In my experience readable code goes a long way.

No comments yet, be the first to comment!

How To Make Android Spinner Options Popup in a Dialog

By , 28 May 2014

The earlier versions of Android always showed spinner options in a dialog. Then I think devices got bigger and the powers that be decided to use a dropdown list for spinner options instead of a dialog. It's a sensible choice because the dropdowns look and operate better than the dialog - sometimes.

It's the sometimes that is the problem. For lists with a lot of data it makes better sense to use a full screen dialog than a small dropdown. The screenshots below show how much better the usability of a dialog selection is for our app, The Game Of Your Life.


1 comment, post a comment!

How To Remove The Facebook Android Sharing Intent

By , 2 May 2014

I don't really care why Facebook made a mess of their Android sharing intent. Other developers have already pointed out that their iOS sharing works fine, but their Android product only allows you to share URLs and not text. My guess is they are trying to force developers onto their API because, you know, force makes people so much more motivated and creative and all that.


The real problem is that it makes your app look broken, and fortunately there is a simple solution:

Dump Facebook.

Just cut and paste the code below to launch your share intents and Facebook will not be listed as an option. That way you won't look bad when their share page comes up blank.


No comments yet, be the first to comment!

How To Catch A ClassNotFoundException

By , 13 April 2014

HTTP request from spammers trying to exploit our commenting system is causing a ClassNotFoundException deep in the JSF / Java code. Specifically, spammers are sending serialized objects in the javax.jsf.ViewState parameter for classes that no longer exist in our system. The resulting exception is this:


No comments yet, be the first to comment!

10 Cloud Storage and Backup Services

By , 17 January 2014

The company that was storing our backups online was always a little flakey and recently the scripts just started failing altogether. Time to find a new backup service methinks.

Since I first looked at backup online the market has changed a lot. It's now called "cloud storage" for starters, and the number of companies providing competing services has ballooned.

Here is a quick comparison a few key players on the market, and the monthly cost of storing 100GB of backups. The server being backed up is running Ubuntu, so the last column shows how the backups are run. Rsync is our preferred method.


No comments yet, be the first to comment!

< Prev1 2 3 4 5 6 7 8 9 10 11 12 13 Next >

Join The Mailing List

Subscribe to our mailing list for the latest news and announcements.

Follow Ninth Avenue

Website Updates