Creating and filling MySQL database tables

In a previous posting I summarized the steps required to start your own private instance of the MySQL daemon. Hibernate needs a few things more than just the bare DBMS instance, however. You will need a database, at least one table in it, and a user with permission to access that table. The user is not strictly needed. The user «root» has full permission to do anything. And this is precisely the reason why to create a dedicated user.

All the stuff following is not fit for a production environment, due to the lax security precautions.

Creating a database and and user

Creating a database is very simple. Connect to your daemon:

mysql -u root mysql

This will only work if the Unix socket or IP-address and port are specified in either a configuration file or by a environment variable. Then, from within the mysql client, create a database,

create database simple;

and a user:

create user 'hibernate'@'localhost';

My user «hibernate» may only connect from the local host. He has no password set, though. I am not going to change that, as it is more convenient during development.

Then grant him full permission on the database you created earlier:

grant all privileges on simple.* to 'hibernate'@'localhost';

The MySQL manual explains the whole topic on creating and confining users in greater detail.

Filling the database

Initializing a database is a cumbersome and error-prone task. It usually involves sending quite a few SQL commands to the DBMS instance. All the repeatedly needed stuff better goes into a plain text file for re-use. By convention one with the file extension «sql». I have one sql file that creates the tables. It is named «simple_db_create_tables.sql» and looks something like this:

drop table if exists songs;
drop table if exists albums;

create table albums (
  id integer primary key auto_increment,
  title varchar(20),
  publisher varchar(20)
);

create table songs (
  id integer primary key auto_increment,
  title varchar(40),
  artist varchar(20),
  performer varchar(20),
  album integer,

  foreign key (album) references albums (id)
);

Yes, it is about music, as my Android example. Another sql script loads sample data from a text file. It utilizes the «load data» statement for that purpose. My sample data is arranged as list of comma-separated values, one file per table to be filled, e.g. in this example one for albums and one for songs. The album data file contains this:

Contains sample values for the "simple" database table "albums".
Values separated by comma. First two lines ignored.
1, Distraction Pieces, Speech Development
2, A Canticle for Leibowitz, "Blackstone Audio, Inc."

The «load data» statement is quite flexible as to the structure of the input data. My CSV files are loaded by statements like this:

load data local infile 'simple_db_albums.csv'
      replace
      into table simple.albums
      fields terminated by ','
      ignore 2 lines;

Did you wonder why the data file above explicitly sets the «id» field for each row? Should not «auto_increment» accomplish that? Well, it would. Just leave the first field empty and it kicks in. But «id» is a foreign key used by the table «songs». That means each song must give the exact id of its album. Furthermore, subsequent loading of the CSV-file would happily insert row after row of the same two albums into the database, each having a different id.

All scripts can be executed with the «mysql» command-line tool, e.g.:

mysql -u hibernate simple < simple_db_create_tables.sql

The tutorial chapter of the MySQL documentation explains creating and filling tables much more thoroughly.

Eclipse SQL Explorer

All the things above—and much more—can be accomplished conveniently from within Eclipse with the help of the Eclipse SQL Explorer extension. Installing the JDBC-driver for MySQL (or any other DBMS) is—while not overly complicated–also not exactly trivial. Mark E. DeYoung tells you what needs to be done.

SQL Explorer sucks however when it comes to executing «load data» statements, though not entirely to its own fault. The file-name given to that statement can be either an absolute or a relative path. In the former case the behavior of «load data» is pretty obvious. In the latter case however, the path is interpreted relative to the directory where the executing command—the mysql client or in this case Eclipse—has been started. With the mysql client you would simply change directory to where the data file resides, but as for Eclipse this is not within you power. Most likely Eclipse had been started with your home directory as current working directory, and this is where «load data» would resolve the relative path from. Setting the path of «load data» relative to your home directory will most likely not be viable because anyone else sharing the sql script would have to have the data file at the exact same relative path within their home directories too. For the same  reason absolute paths fail altogether.
In consequence, it means that for loading data the «mysql» command is still the best.

With tables created and filled, one can proceed to finally accessing them from a Java program. But not in this posting.

Updates

08/14/2012, 21:00 UTC Changed «grant all privileges on simple…» to «grant all privileges on simple.*…» Otherwise MySQL would interpret «simple» as a table name in the currently selected database, which would most likely be the «mysql» database.
Also, my MySQL user is no longer called «eclipse» because it is mostly not Eclipse accessing my database. Chose «hibernate» instead.

Showing Delicious booksmarks in a Blogger widget

I just added a widget to this blog which shows my recent Delicious bookmarks. My first thought was to look for a dedicated Delicious widget, but there seems to be none in Blogger's widget library. Google search lists a few rather dated do-it-yourself articles. They all stem from a time before the «new» Delicious. That is why I did not even attempt them. It is much easier to show the bookmarks with the off-the-shelf RSS-widget.

There was as slight problem however. I mark all the bookmarks relating to the topic of this blog with a «The Convalescent Coder» tag. I could not see a direct way to show the URL for just this restricted RSS-feed. The obvious appoach seemed to be to take the browsing URL which was «http://www.delicious.com/jhunovis/the-convalescent-coder» and insert «v2/rss» just before the user-name. Surprisingly this produced an empty feed—but only for tags that contain spaces. Some unsuccessful web-searches later, I simply tried to replace the dashes by escaped spaces, and—it worked. For instance, the RSS feed for my bookmarks tagged «The Convalescent Coder» is «http://www.delicious.com/jhunovis/the%20convalescent%20coder».

Running a private mysqld instance and keeping the databases in your home directory

I needed a DBMS running for some experiments with Hibernate. I wanted to use the
packages delivered by Fedora but did not want to run it as a system service,
because this usually requires special user permission; in the worst case root
permission. Running the DBMS as a system service has further
disadvantages. Programmers are of the lazy folk and tend to run the service at
boot time. It saves a few keystroke. It also wastes computing resources, opens
potential security holes into your computer, and slows down the start-up time of
the system. As a further detriment this approach fails on systems where you
do not have administrator permissions. You know, the ones at work. So, it is best
to run it as local user from the start.

I chose MySQL for my DBMS for no other reason that I have used it before
somewhere in far past. Installing it on Fedora is simple:

su -c "yum install mysql-server"

The MySQL manual contains detailed installation instructions for all major
distributions.

Then copy the default MySQL configuration file at /etc/my.cnf into your home
directory as ~/.my.cnf and adapt it. Mine is looking like this:

[mysqld]
datadir=/home/jhunovis/tmp/mysqld
socket=run/mysqld.sock

[mysqld_safe]
log-error=log/error.log
pid-file=run/mysqld.pid

It overrides the settings of the global configuration file. The «datadir» is the
location where MySQL stores its run-time data, like the actual databases, but
also log-files and the Unix socket. Note that all the file options are relative
to «datadir». Unfortunately, the tilde is not accepted for referring to a home
directory.

Update 08/09/12, 21:30 UTC: I just realized, that the command «mysqlshow» will show the directories «log» and «run» as databases, which is awkward, if not disturbing. I then decided to place all the non-database files into a sibling directory of the datadir. End update.

Update 08/10/12, 13:15 UTC: When I was filling some tables with sample data I
saw that my database used the character set «latin1» instead of
«utf8». Furthermore it used * Swedish* for collating, which was just weird (I am
far from Sweden). Adding the line «character_set_server=utf8» to the
configuration file fixed this. You can check your character settings by issuing:

mysqladmin variables | grep -E 'character|collation'

End update.

You will have to create those directories prior starting the MySQL
daemon:

mkdir -p ~/tmp/mysqld/{run,log}

Starting mysqld at this point will fail because the system databases are still
missing. Create them by issuing on the command line:

mysql_install_db

Now start the service:

mysqld_safe

Then you can connect to your database:

mysql -u root -S ~/tmp/mysqld/run/mysqld.sock

You have to pass the socket to the MySQL client to which it should connect to.
Alternatively, you may add a section like the following to your ~/.my.conf that
saves you the trouble:

[mysql]
socket=/home/jhunovis/tmp/mysqld/run/mysqld.sock

You will have to repeat this for every MySQL command you
use. Better point the shell environment variable «MYSQL_UNIX_PORT» to that
socket file and be done with it once and for all:

export MYSQL_UNIX_PORT=~/tmp/mysqld/run/mysqld.sock

Your private MySQL service instance is now ready. You may now test it, adjust
permissions, and create a database for your own purposes.

Update 08/10/12, 13:15 UTC: Changed title from «Starting a private mysqld instance» to «Running a private mysqld instance and the keeping databases in your home directory».

Never start or shutdown a service in android.test.ServiceTestCase.setUp() or tearDown()

Setting up your service setUp() or stopping it in tearDown() will break your test-case. Most probably a NullPointerException will be thrown somewhere. Both errors happen independently, but have in essence the same cause: the internal state keeping of ServiceTestCase.

The class Service does not keep track of the service life-cycle state, i.e. whether the service has been created, started, or is about to be destroyed. That is why the ServiceTestCase defines a number of state variables for tracking the state: mServiceAttached, mServiceCreated, mServiceStarted, and mServiceBound. The problem lies with mServiceCreated. Let's check it out in detail.

Calling startService() in setUp()

Starting the service in setUp() might cause an error during testServiceTestCaseSetUpProperly()—and for that matter, during any other of your own tests.

In my case it was only testServiceTestCaseSetUpProperly() that failed with an error. It is the sole test-case defined by ServiceTestCase. Its purpose is to detect improperly configured tests. I does only one thing: calling setupService().

My service was still very rudimentary, resembling the local service example from the API docs of class Service. The test failed during the tearDown(). Per default, tearDown() shuts down the service. This includes invoking onDestroy() on the service. There occurred a NullPointerException. The implementation of my onDestroy() method is as follows:

@Override
public void onDestroy() {
    // Cancel the persistent notification.
    mNM.cancel(NOTIFICATION);
    // Tell the user we stopped.
    Toast.makeText(this, R.string.music_player_stopped, Toast.LENGTH_SHORT).show();
}

Surprisingly the variable mNM (of class NotificationManager) was null, though it was initialized properly in onCreate():

@Override
public void onCreate() {
    mNM = (NotificationManager)getSystemService(NOTIFICATION_SERVICE);
    // Display a notification about us starting.  We put an icon in the status bar.
    showNotification();
}

So, when did it went null again? Well, never! Due to calling startService(…) in setUp() it just never had been initialized! It was initialized once, all right. And that was during startService(…). But right after that, testServiceTestCaseSetUpProperly() called setupService(), which will create a new service instance, but will not start it—i.e. onCreate() will not be invoked upon it. So my precious—and initialized—service instance was silently exchanged against an uninitialized one. That alone would not be fatal, were not the state variable ServiceTestCase.mServiceCreated still set to true, thus signaling that onCreate() had been invoked on the service. And because of that, tearDown() will happily invoke onDestroy() upon a service instance whose onCreate() method never had been called. Smells a bit like a bug to me…

Calling shutdownService() in tearDown()

In the case of the shutdown the cause of the error is easy to pinpoint: ServiceTestCase.tearDown() also shuts down the service—and the API docs clearly say so. It is understandable that a second attempt will fail, finding its underlying service gone. Or is it? The error happens in ServiceTestCase.shutdownService(), in line 10 of the following listing:

protected void shutdownService() {
    if (mServiceStarted) {
        mService.stopSelf();
        mServiceStarted = false;
    } else if (mServiceBound) {
        mService.onUnbind(mServiceIntent);
        mServiceBound = false;
    }
    if (mServiceCreated) {
        mService.onDestroy();
    } 
}

The call to ServiceTestCase.tearDown() has just executed this very same code once and afterwards nullified the service instance. However, mServiceCreated is (again) still true, although the service is already destroyed. In fact, this status variable is never re-set to false anywhere in ServiceTestCase. Thus onDestoy() will be invoked on a null reference.

Conclusion

The moral is, never call startService(…) in setUp() and never shutdownService() in tearDown(). It would be nice however, if the official documentation would impress a little more upon that. Also, a little less unforgiving implementation seems possible. Basically that would mean to just re-set mServiceCreated to false in two or so places.

Backlight control causes hard reboot on Macbook Pro

Since installing Fedora on my aged first-generation Macbook Pro I was plagued by intermittent reboots. They mostly occurred during the boot process, while logging into KDE, and—less often— when the system was up and running. No need to mention this was a disaster and it pretty much cured me from using a file system that deems a fsck-tool of no great import.

At least for the latter crash-type, I was able to pin-point the culprit: it was the black-light control. Whenever KDE dimmed the panel there was a solid chance the system would reboot—no kernel panic, no stopping. Not even kdump would break its fall. Sure enough, disabling dimming would cure that.

Display dimming can be disabled in KDE's «Engery Saving Settings»

Now I am waiting for the next crash during reboot before blacklisting the «apple_bl» kernel driver. But maybe the Linux kernel 3.4 that ships with Fedora 17 has mended this issue already. Lets wait and see.

Eclipse ADT: Conditional Breakpoint Error

When I tried to understand the way Android's RelativeLayout measures and re-sizes the views it contains, I had do debug the Android SDK sources. Eclipse makes that quiet easy. All you have to do is attach the sources of the SDK you are using to the «android.jar» in your project's preferences.
Stepping through the source and setting breakpoints worked flawlessly. But when I wanted to set a conditional breakpoint (because I wanted to stop execution when RelativeLayout.onMeasure measured a specific sub-view), Eclipse failed with an error:

Conditional Breakpoint Error

It reads: «Conditional Breakpoint Error. Conditional breakpoint has compilation error(s). Reason: Unable to compile conditional breakpoint - missing Java project context.» Also, evaluating and displaying Java expressions would not work and gave the following error:

Error Evaluating

It reads: «Error Evaluating. Unable to evaluate the selected expression: To perform an evaluation, an expression must be compiled in the context of a Java project's build path. The current execution context is not associated with a Java project in the workspace.» Evaluated expressions come in handy, for instance, when picking the MeasureSpec of a View apart, which is a bit-field composed of a length and a mode.

Searching the internet for a solution, I only unearthed the rather vague suggestion to—surprise!—create a Java project for the Android library sources. I was not really fond of that idea, subdued by the size of the library. Not without reason, as I saw later. So I asked for a solution over at stackoverflow.com, but to no avail. I suppose the problem is to specific. When nothing came out of it, I had to swallow the pill and create a project for the sources of the Android library. And guess what? It fixed the problem!

Step by step do the following. First create a new Java project. A wizard dialog will pop up. Name the project as you like, «Android library sources» seems appropriate. On the next page, link an additional source and point it to the appropriate source directory in the Android SDK. Eclipse will now ask for «Inclusion and Exclusion Patterns». Exclude everything by adding the asterisk «*» to the exclusion pattern. This both surprising and important. Surprising because Eclipse does not need to do anything with the sources (like compiling) for conditional breakpoints and the like to work. And it is important, because otherwise your log will overflow with warnings, and error and info messages. Finish the dialog.
Now you need to attach the source code to the «android.jar» library of your project. Open your project's properties. You will find the library under «Java Build Path» on tab «Libraries» as show by the following screen shot:

Attaching the source code to the Android library

I my original configuration I had attached the source from an «External Folder» instead of a workspace, which only worked partway.

And that's it!

Android ImageView and BitmapDrawable: gravity and scaling to fit

This article explores whether it is possible to display a square picture (for instance the cover of a music album) in a way that it uses the whole width of the display and keeps its aspect ratio. No programming must be involved, i.e. a pure XML-layout definition is the goal.

The problem of proportional scaling

Milestone 1 of my journey into Android software development is creating a rudimentary GUI for a MP3 player app. On the way towards it I soon ran into a first problem, naturally. The cover image (in the mockup represented by compact disc) would not display as intended. It was supposed to fill the screen width, scaling proportionally in height (assuming square dimensions of the cover and portrait orientation of the phone). And though the BitmapDrawable scaled (down) correctly, the enclosing ImageView would never wrap it up in height. In fact, it would be so large that it even pushed the SeekBar out of screen:

Dream (left) and reality (right): the ImageView does not adjust its height to the picture of the compact disc. Its actual dimensions are marked by the blue border!

My first attempt to layout the ImageView is shown by the following code snippet. The cover image sits inside a RelativeLayout (not shown) and is aligned below the song title (line 5).

<ImageView  
       android:id="@+id/cover"  
       android:layout_width="match_parent"  
       android:layout_height="wrap_content"  
       android:layout_below="@id/songtitle"  
       android:src="@drawable/cd" />  

I assumed that setting layout_width to to «match_parent» would scale the image to fit in width, which it seemed to do. Furthermore I assumed that setting layout_height to «wrap_content» would shrink the view closely wrap the image, i.e. would result in a ImageView of square proportion. It did not, obviously.

I am not going to retrace in detail how I went trial-and-error through the numerous properties of the Drawable and ImageView classes. Lets approach it more systematically, because there is more than a single factor in effect here. The source code for this project is available at GitHub. I will created tags for each point discussed in this blog.

For this experiment, I created three square images of varying dimensions: one too small for a WVGA800 screen (which is 480×800px in the case of the Galaxy S2), it is 100 pixel wide and tall; another of exact fit (480px wide); and a third images too large for the screen (800px wide). The vanilla layout looks like this for these three image sizes:

Android prevents clipping of over-sized images and rather scales them down. The blue border marks the bounds of the ImageView.

Only for the picture which is too small for the screen the ImageView behaves as expected (by me, at least). For both the exact fit and the over-sized picture the ImageView does not wrap its content. Android also scales down the over-sized picture to fit the screen, which is a sensible precaution. The small image however is not enlarged automatically, probably because it a) might look terrible, b) does no harm to leave it as is.
Now lets play with the properties of the user interface components.

BitmapDrawable gravity (and tiling)

Poking around the API documentation, I first stumbled upon the gravity attribute of the BitmapDrawable class. It looked promising, offering options for pushing a picture toward any edge of the container, or scaling it, or clipping it. Surprisingly, none of the options had any effect on the scaling and placement of the sample images from above. For both of the larger images the API documentation might explain:

The gravity indicates where to position the drawable in its container if the bitmap is smaller than the container.

But why then does it not work for the small image either? It turned out, that the phrase «in its container» misled me. The source code of BitmapDrawable.draw(Canvas) reveals that gravity only applies within the bounds of the BitmapDrawable itself. The ImageView sets these bounds via Drawable.setBounds(int,int,int,int) to the exact dimension of the underlying bitmap image. There is only one case in which the bounds actually matches the proportion of the ImageView and this is when ImageView.scaleType==FIT_XY («fitXY» in a XML-layout) in the private method ImageView.configureBounds().

The scaleType of class ImageView defaults to «FIT_CENTER». To make use of gravity, it must be set to «fitXY». Furthermore the src-property of the ImageView must refer to a bitmap XML-definition and not to an image file (res/layout/toosmall.xml):

<ImageView
    android:id="@+id/cover"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_below="@id/songtitle"
    android:scaleType="fitXY"
    android:src="@drawable/toosmall" />

Then the gravity of the BitmapDrawable can be configured with something like this (res/drawable/toosmall.xml):

<?xml version="1.0" encoding="utf-8"?>
<bitmap xmlns:android="http://schemas.android.com/apk/res/android"
    android:src="@drawable/square100"
    android:gravity="fill_vertical"
/>

That means gravity only affects the appearance of the picture if the bounds of the Drawable are actually larger than the physical dimension of the picture displayed. Apart from ImageViews with ScaleType.FIT_XY this is the case for View backgrounds. Best use an empty, fully expanded LinearLayout for experiment, as I did for the following screenshot.

As background image, a DrawableBitmap receives enough room for gravity and tiling to have an effect. From left to right: gravity="right|center_vertical", gravity="fill_vertical", tileMode="mirror".

The gravity property has further oddities. Especially the properties clip_vertical and clip_horizontal behave completely opposite to intuition as +Lorne Laliberte points out.

ImageView scaleType and adjustViewBounds

The scaleType has a major say on the placement of a Drawable inside an ImageView (read the Andre Steingress' blog posting on its various effects). But disabling scaling via «fitXY» in order to make gravity take effect will not solve the problem of the view area being larger than the contained bitmap! No combination of gravity flags will scale the bitmap proportionally! So gravity was a red herring after all. It is the property «adjustViewBounds» in combination with the default scaleType that will do the job.
[Update 2012-05-25]: When adjustViewBounds is true, the scaleType no longer matters: ImageView will reset it to FIT_CENTER (the default) anyway.

The adjustViewBounds property will shrink the ImageView to fit its content. it has unwanted side-effects, though.

Now cry «cheat!» The larger images look fine, but the small image stubbornly retains it dimensions, and is not scaled to fill the width of the screen. Unfortunately, there seems to be no way to fix this by a XML-layout definition alone, programming is required, and I am defeated for now. I will explore the different approaches in a later posting.
If someone knows a way to archive it by pure XML, I would be grateful to hear about it!

Conclusion

There seems to be no way to proportionally scale an ImageView to match its parents width by means of a XML-layout definition alone. Bitmaps which are larger than the available display space will be reduced in size correctly but smaller images never enlarge proportionally. The most obvious solution would be to provide fitting or over-sized bitmap images for each targeted device. But this is neither future-proof nor always possible—think of the cover images which usually come in just one size, attached to a MP3-file. But then again, dynamically loading images always requires some programming anyway. So a few extra lines of code might not matter too much.