Android – Projection Maps explained

Android comes with the SQLite database. Although it’s a lighweight DBMS, its authors are sufficiently proud of it that instead of listing what features it has, they prefer to list what features it doesn’t!

In addition to the SQL support we all know and love, Android includes some SQLite features that aren’t really well explained. First and foremost of which is the SQL Query Builder. Although nominally it’s a way to construct SQL without “knowing SQL”, I suspect its more important feature is that it makes it easier to construct SQL from context URIs.

One item, however, which has proven to be really frustrating is the Projection Map. Actually, I’m not even where they pulled the term “projection” from, but Projection Maps have been a real poser for me. I finally broke down and looked at the SQLQueryBuilder source code. A reminder: a ProjectionMap is a Map<String, String>.

Here’s what I found.

If you read the JavaDocs, you’d get the impression that Projection Maps effectively provide a “poor man’s database view” where you can simplify selection request items (projections). In actuality, it’s not so straightforward. Or maybe it is, but the explanation isn’t. It doesn’t help that the literature is rife with projection maps whose sole purpose in life seems to be to convert something to itself: “table1._id” => “table1._id” and so forth.

First of all, you don’t even need a projection map unless you want one. Just don’t set it. If you take that approach, the projection names are processed “as is”, exactly as passed in via the projection array.

Secondly, even the projection array is optional. If you pass null, it generates a “SELECT *” operation. In general, that’s not recommended in SQL, because of the breakage that can occur if columns are added/removed, or re-ordered, but that option is still available if you need it.

Thirdly, the projection map, if supplied applies only to the selection list! In other words, you could map “store_name” => “bookstore.storename”, and it would result in “select bookstore.storename …”, but if you supplied “store_name” in the “order by” query builder parameter or one of the other qualified parameters, you can potentially end up with bad SQL.

Fourth, the projection map can be overruled. If the key part of a projection entry contains an “as” clause, the value part is ignored and the key part is inserted into the resulting SQL. For example “parrot as polly” => “cracker” will generate “SELECT parrot as polly …” and no crackers. The “as” can be either all upper-case or all lower-case (not mixed case) and must have at least one space before and after the word “as”.

Fifth, if you provide a projection map and no projection array, the projection map’s value entries are used to generate the select item list and the projection map’s keys are ignored. In other words, “parrot as polly” => “cracker” will result in “SELECT cracker, but only if no projection array (zero elements or null) is supplied. It’s not a safe thing, however to make a map like “parrot as polly” => “cracker as graham”.  At the moment, that would result in “SELECT cracker as graham”, but only if no select array was supplied. But that’s probably an artifact, not an intended behavior.

Sixth, and finally, If you do provide both a projection map and a projection array, each and every element in the array must appear as a key in the map! Any omissions will cause the SQLQueryBuilder to throw an IllegalArgumentException (Invalid column) when it attempts to build the SQL. That one’s mentioned in the JavaDocs, by the way.

Now you know. And so do I.

Android ListViews with Checkboxes

Android has a very useful way of organizing lists: the ListView. However, it doesn’t seem to work right when you include a checkbox in the list!

Actually, it turns out that it works exactly right, but only if you understand what “right” actually is.

The normal behavior of a ListView item is that you can highlight it and click it and the whole item line participates. Not so when the line includes a checkbox. Why? http://www.mail-archive.com/android-developers@googlegroups.com/msg11390.html

Which says, in brief: If a Listview contains an item that can receive focus, that item receives events instead of the listview.

It turns out that that is literally true. Set the “focusable” attribute on the CheckBox to false and the magic returns: http://stackoverflow.com/questions/1121192/android-custom-listview-unable-to-click-on-items . Don’t forget to manually set the “checked” attribute when you handle the list item click!

Here’s an interesting bit of code: http://www.anddev.org/checkbox_text_list___extension_of_iconified_text_tutorial-t771.html

Using the Apache OpenJPA command-line tools

Stuff that’s unfortunately not concentrated into a convenient ready-to-use example in the Apache OpenJPA docs. But that’s what this blog is all about!

When using the reverse engineering, schema, and other tools directly from a shell script (not Ant or Maven), the default place to get datasource definitions and related options is in META-INF/persistence.xml. This file is mandatory even in cases where you don’t actually connect to the database, such as generating Java source from an XML schema (reverse generation).

Because the tools are using a validating parser, a schema name is REQUIRED. Example, supplying the JPA schema via the xmlns attribute:

<?xml version="1.0"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
  <persistence-unit name="openjpa">
    <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
    <properties>
      <property name="openjpa.ConnectionURL" value="jdbc:hsqldb:tutorial_database"/>
      <property name="openjpa.ConnectionDriverName" value="org.hsqldb.jdbcDriver"/>
      <property name="openjpa.ConnectionUserName" value="sa"/>
      <property name="openjpa.ConnectionPassword" value=""/>
      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO"/>
      <property name="openjpa.jdbc.DBDictionary" value="StoreCharsAsNumbers=false"/>
    </properties>
  </persistence-unit>
</persistence>


CAUTION:
You should delete the orm.xml file when re-running these tools. Otherwise they will use the old copy, which may not be in sync with your current efforts.

JSF Submit-on-Enter forms

It’s not (so far as I know) officially required anywhere, but it’s a generally accepted principle that a form on a web page containing one text field and one submit button should submit itself when you type in text and press the ENTER key. A classic example of this is Google .

In actuality, it isn’t quite that simple. For totally unknown reasons, Microsoft Internet Explorer requires at least two text fields and this apparently goes all the way up to and including IE8 (at least when you upgrade from IE6). Fortunately, the “spare” text field can be invisible and ignored by the application.

JSF is more complicated, and AJAX-aware JSF such as RichFaces compounds the issue even more. Here’s a solution that seems to work for me:

  <h:panelGroup>
     <h:inputText style="visibility:hidden;display:none;"
        disabled="disabled" required="false" />
     <a4j:commandButton id="ask"
        type="submit" styleClass="button1"
        action="#{forgotPassword.doAskQuestion}"
        reRender="pnl1,fpMsg" value="Submit" />
 </h:panelGroup>

I am indebted to Lincoln Baxter for this particular hidden field definition (it’s quite touchy). He’s the person behind the JSF PrettyFaces product (ocpsoft.com).

Note that this is only a kludge, and may not always work. Additionally, it causes the RichFaces a4j:commandButton oncomplete action to misbehave under both IE and Firefox (though in different ways), as of RichFaces 3.4.

Eclipse: Tomcat FileNotFoundException

After happily using the sysdeo Tomcat plugin for years. I’ve finally been pushed into using the Server facility bundled into Eclipse Ganymede’s JEE drop. For some reason, once started, sysdeo cannot be stopped or restarted – it loses track of the logfile classes.

In theory, you just create a new server of your choice in the Eclipse Servers View. In practice, it’s not quite so simple. Here’s why:

Tomcat actually consists of 2 aspects: a sharable codebase (CATALINA_HOME) and a per-instance part (CATALINA_BASE). This allows multiple copies of Tomcat to run on a single server using one codebase. Most of the time, CATALINA_HOME and CATALINA_BASE are the same value, since more often than not, only one copy of a given version of Tomcat is is use – at least on a developer’s machine.

When you create a Tomcat server using the Eclipse Servers facility, however, it clandestinely creates its own CATALINA_BASE, copying selected files – and only those files into a directory owned by the plugin.

I got burned. I was keeping a file of my own in CATALINA_HOME and using a relative reference to it in server.xml. The file didn’t copy and Tomcat didn’t start clean.

The simplest solution was to edit server.xml and replace the relative path to an absolute path, so that the copied configuration would be able to locate the original (and in this case, the only) copy of my file.

Apparently, however, the copying of the CATALINA_BASE data occurs only when you create a new Server definition. I had to delete the old Server definition from Eclipse and create a new one to get the changes to take.

Setter not found for property class

I hate this. Messages like this shouldn’t be permitted. The proper format should be

Setter not found for property “class”

In other words, a JSF tag was coded with a “class=” attribute. So actually, an even more precise rendering should be:

No valid setter method exists for attribute named “class”.

In a civilized world, the element name and line where the offense was committed would be reported as well.

At any rate, the cause of the error is that the attribute should have been “styleClass”, not “class”.

Trac: Permission denied connecting to PostgreSQL Server

Another one of those obscure things that a web search didn’t turn up. Using the “psql” command-line utility, it was possible to connect to and access the PostgreSQL Trac database. However, the webapp failed, with “cannot connect to server: Permission denied”. No amount of tweaking the firewalls or the pg_hba.conf file would help.

It turns out that the permission wasn’t PostgreSQL, it was selinux. It was forbidding psycopg from opening a database connection while running inside Apache. It wants a “allow httpd_t postgresql_port_t:tcp_socket name_connect;”.

A message shows up in /var/log/audit/audit.log – if you think to look there.

PrettyFaces downstream effects

Latest in the ongoing story of why even the simple things take much longer than they should:

I’m working on an app where the menus generate PrettyFaces bookmarkable URLs. However, when I jump to the target page, then click on a commandLink, the secondary target page won’t post properly. When you click on the actionButton, it throws a ViewExpiredException for the target page’s View.

A look at the secondary target page source, however, indicates that the POST URL is the original PrettyFaces URL. NOT the secondary page URL. JSF can be so much fun that way.

The cure is to attach a <redirect/> to the navigator action that dispatches the secondary page. This will cause the correct URL to be used.

UPDATE: It’s a Bug!

Lincoln Baxter, III is the author of PrettyFaces and when he heard about this, he made some mods to PrettyFaces which should remove the need for the <redirect/> workaround. If you’d like to try his fix, it’s in his 1.2.4_PR1 release. Thanks, Lincoln!

Myfaces Extensions Validator and RichFaces (Not yet)

The MyFaces extensions validator package looks like someday it will make life a lot more pleasant when designing JSF backing beans. Instead of cluttering up the markup with cumbersome extra XML, the MyFaces Extensions Validator allows you to annotation the backing bean properties. It even adopts the applicable annotations (such as nullable=false) on JPA model objects.

Unfortunately, when I added it to my RichFaces/PrettyFaces project, all the RichFaces AJAX functions stopped working. Apparently the annotation processor got in the way of the JavaScript downloading process. The AJAX support functions were not found and the Richfaces Calendar stopped responding to popup click requests – Understandably, since the JSF Calendar object was no longer found.

So, I have to do my validations the hard way for the moment.

org.hibernate.PropertyAccessException: could not get a field value by reflection getter of com.mypackage.MyEntity.entityId

There are reports that messages of this sort were due to bugs in one or more Hibernate releases. But this is also a legitimate error.

I wasted a lot of time before it hit me. The original code was:


@SuppressWarnings("unchecked")
public List<State> getStatesForCountry(String countryID) {
    final String SQLCOMMAND =
        "SELECT c " + "FROM "
            + State.class.getSimpleName() + " c "
            + " WHERE c.parentCountry = :countryID"
            + " ORDER BY c.StateName ASC";

    Query query = entityManager.createQuery(SQLCOMMAND);
    query.setParameter("countryID", countryID);
    List<State> results = query.getResultList();
    return results;
}

It should have been:


@SuppressWarnings("unchecked")
public List<State> getStatesForCountry(String countryID) {
final String SQLCOMMAND =
    "SELECT c " + "FROM "
         + State.class.getSimpleName() + " c "
        + " WHERE c.parentCountry = :country"
        + " ORDER BY c.StateName ASC";

    Query query = entityManager.createQuery(SQLCOMMAND);
    Country country = findCountry(countryID);
    query.setParameter("country", country);
    List<State> results = query.getResultList();
    return results;
}

Where findCountry is simply an entityManager.find(Country.class, countryID);

This is a sneaky one because when the database is laid out, you think in terms of foreign keys. But in ORM, you don’t see those keys directly – they translate to object references. So the original version was attempting to compare an object to the key of the object instead of an instance of the object.

I can only plead distraction. The object model in question had been designed by someone else. Instead of accessor methods, it was using direct field access (which I avoid for a number of reasons). Further aggravating the issue was that the fields were all given names starting with an upper-case letter as though they were independent classes instead of properties.

But even without distractions, it’s not too hard to make this mistake.