UUID Converter For Vaadin

Here is a class to convert a java.util.UUID object to String for use in Vaadin. Originally written by a team member at Vaadin, I modified their source code to output lowercase hexadecimal characters as required by the UUID spec.

I filed a feature request ticket with Vaadin to bundle such a class with Vaadin.

Source code…

import com.vaadin.data.util.converter.Converter;
import java.util.Locale;
import java.util.UUID;

/**
 * Modified by Basil Bourque to ensure output of hex string is in lowercase as required by the UUID spec. 2014-08.
 *
 * @author petter@vaadin.com
 */
public class UUIDToStringConverter implements Converter<String , UUID>
{
    private static final String NULL_STRING = "(none)";

    @Override
    public UUID convertToModel ( String value , Class<? extends UUID> targetType , Locale locale ) throws ConversionException
    {
        try {
            return value == null || value.isEmpty() || value.equals( NULL_STRING ) ? null : UUID.fromString( value );
        } catch ( IllegalArgumentException ex ) {
            throw new ConversionException( ex );
        }
    }

    @Override
    public String convertToPresentation ( UUID value , Class<? extends String> targetType , Locale locale ) throws ConversionException
    {
        return value == null ? NULL_STRING : value.toString().toLowerCase(); // The UUID spec *requires* hex-string output to be lowercase. Must tolerate uppercase for input.
    }

    @Override
    public Class<UUID> getModelType ()
    {
        return UUID.class;
    }

    @Override
    public Class<String> getPresentationType ()
    {
        return String.class;
    }
}

xx

Installing Postgres 9.4

Here's a reminder checklist of the steps I take when installing successive beta versions  (1, 2, and 3, so far) of Postgres 9.4 on my Macs.

Installer

The Postgres support company EnterpriseDB graciously supplies installers for Mac OS X as a courtesy to the community. You may reach their site via the Download page of the usual Postgres site. For beta versions look for the paragraph labeled "Beta/RC Releases and development snapshots (unstable)", and find the link to click. On the next page, look for the link near the text "offsite link". Currently takes you to the Early Experience page of EnterpriseDB. Click a link to download a DMG file named something like "postgresql-9.4.0-beta3-osx.dmg".

Users

The Postgres superuser "postgres" is already created by the installer.

Next we need to create a not-quite-so-super user. This is done in "Login Roles" in pgAdmin, not the "Group Roles" list. Choose a name and password for this user, and write it down. The create this user in pgAdmin using a dialog or run the following SQL. After creation, refresh pgAdmin, and context-click the user to choose "Properties" where you can define a password  on the "Definition" tab.

CREATE ROLE your_admin_user_name_goes_here LOGIN
  NOSUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;

Next, create a group role to be used by your application. Let's imagine your app is named "Example" in general and "example_" in Postgres. We move attention from "Login Roles" to the "Group Roles" list in pgAdmin. Again, do this in the pgAdmin wizard-like dialog or use this SQL.

CREATE ROLE example_app_role_
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

As done above for the admin user, context-click the user to choose "Properties" where you can define a password  on the "Definition" tab. And, of course, you are writing down these passwords.

Now create a user to be assigned to that role. Again, use either dialog or this SQL:

CREATE ROLE example_app_ LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT example_app_role_ TO example_app_;
COMMENT ON ROLE example_app_ IS 'For connections from our Example app.';


Restore

As I am installing a succession of beta versions, I already have a backup of my desired database. I used pgAdmin's "Backup" feature to create a .backup file. That chore is described in my previous blog entry.

That backup contains just about every aspect of my database, except one: Users & Passwords. That is why we defined our admin user, app role, and app user in those steps above. Those users and roles must be in place for the restore feature to work. The restore references ownership of various objects by those users/roles.

So now I want to restore that database to my new Postgres. No go. The restore process cannot create the database. You must create the database manually, such as in pgAdmin. Use the same name, but need take no further steps.

Context-click on the new database to choose the Restore feature. In the dialog, choose the character encoding to match the original. Not sure of this is required, but probably. Use the button to choose the .backup file to be imported. Finally click the main button to execute the restore. The restoration may take a while. Eventually look to the "Messages" tab of the window to show the progress and completion. Look at the bottom of that report to see if any errors or issues arose.

Permissions

Now we have users defined and we have a database restored. Now we have combine them. The app role we defined must be given permission to work with that database. Execute SQL such as the following, after you have done your homework to study such permissions.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO example_app_role_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO example_app_role_;

Done

That's it. Your database should be back in order. Tables and data should be there. Type "table my_table_name_;" in the SQL window to see all rows, as a quick test. Any functions, domains, etc. you defined should be intact.

Alternative: pg_restore

Instead of this backup-destroy-restore process, you can go another route. Postgres has a nifty pg_restore feature for doing major upgrades in place. I've no experience with this yet.

Uninstalling Postgres 9.4

My reminder checklist for uninstalling Postgres 9.4 beta versions. These instructions assume the use of the Postgres installers provided to the community as a courtesy by EnterpriseDB. For more info, read this answer in StackOverflow.com.

Backup

First backup. Use pgAdmin app to select the desired database(s). On each database context-click to choose the Backup command. Go with default options, including "Custom" format which is a strange name for the native binary format. Choosing "Plain Text" creates SQL statements which is interesting but verbose and slow. The only setting you set is to click the button to choose a folder and specify a desired name for the backup file. Include the ".backup" extension yourself. While most Mac apps are built to add an extension, pgAdmin does not.

Run Uninstaller App

In the root folder (not your user home folder), look in Library folder to find the PosgreSQL folder. So that would be: /Library/PostgreSQL/ path. In there find one or more versions of Postgres. Within a version find the app named uninstall-postgresql. Run that app, supply your system password.

Delete Config File

Delete this file:/etc/postgres-reg.ini.

Delete User

The installer created a Unix user to your Mac named 'postgres'. If eradicating Postgres, you may want to delete that user account. For re-installing a new Beta versions of Postgres, I don't bother. The installer seems to tolerate that extant account.

Delete Apps

Check your usual Applications folder. If the PostgreSQL folder remains there, delete it.

Delete pgAdmin Preferences

If you used the pgAdmin app for administering your databases, its preferences file remains. No big deal. I don't know where it lives. Perhaps the Google would tell you.

No Longer Used

In the old days you would delete the file /etc/sysctl.conf. I do not find that file with Postgres 9.4. I suspect the reason is that 9.4 changed dramatically. Previously a Unix setting was needed on your Mac to enlarge shared buffers. Memory for the database cache is now done differently, so that setting is no longer needed. And therefore that configuration file is no longer needed. I have not confirmed this theory, just a guess on my part.

Track Date-Time Of Row Creation & Modification In Postgres

Here is some example code for using Postgres to automatically track when a row is added to a table and when a row is modified.

Solution requires three steps.

STEP 1 — Columns

First, create a column of type `TIMESTAMP WITH TIME ZONE`. Do not abbreviate to `TIMESTAMP` as that is defined by the SQL spec as `TIMESTAMP WITHOUT TIME ZONE` which is almost certainly not what you want, as explained here by Postgres expert David E. Wheeler.

Name the column exactly the same on each table you want updated. To track creation and mod date-times, I use the names:

• row_created_
• row_modified_

In my own names, I avoid abbreviations. Also, I always include a trailing underscore to avoid collision with reserved keywords, as suggested by the SQL spec. 

Those columns are marked `NOT NULL`. Each has a `DEFAULT` of the current moment, set by calling `CLOCK_TIMESTAMP()`. Note that Postgres has three kinds of "now": (1) The actual current moment read from the computer’s clock, (2) When the statement started execution, (3) When the transaction started execution. One could arguably choose any of those three for their own creation and mod values.

You may or may not want a default value for your `row_modified_` column. Some people argue for the precision of the semantics that a new record has not yet been modified. My counter-argument is two-fold: (a) That means allowing NULL values, and I am of the camp believing NULL to be the work of the devil, (b) In my experience, I rarely look at creation date-time, but just scan the mod column where I then find missing values (nulls) to be distracting/confusing.

STEP 2 — Function

Secondly, define a function to generically update any table as long as that table has a column named exactly as we expect.

A "function" is an old-fashioned name for a chunk of server-side code to be executed at run-time. We define this method as a database object, designating a name and so on just like a table or column.

To write a function, we need a programming language more powerful/flexible than SQL. Postgres is capable of running any number of programming languages on the server side, including Java, Perl, Python, and so on. But one language was created expressly for use within Postgres: PL/pgSQL. This language is nearly always included with any Postgres installation ("cluster" in Postgres lingo) whereas the other languages may not be installed by default. Note how our code below declares the language of the function.

The keyword `NEW` provides the generic ability we need to address any table. When the trigger causes the function to run, the word `NEW` represents the table being updated.

Our function calls another function, one of many date-time functions built into Postgres. As mentioned above, these functions vary.

STEP 3 — Trigger

To run that function, we must define a trigger. A trigger is a rule living on the Postgres server that says a function should be run upon certain events happening. In our case, the event we care about is when a record is being modified (`UPDATE` in SQL terminology).

Postgres allows a trigger to run before the row in the actual database is affected by an event operation, or after the event operation. In our case we want to run the trigger before the record is actually updated. Running our trigger after an update would cause an endless loop as our act of setting the current date-time on the `row_modified_` column would be another update which would necessitate our trigger running again, and again, and again forever.

Example Code

Here is some example code  showing the above three steps.

All three steps can be rolled into a single SQL operation inside a transaction. Note the `BEGIN` and `COMMIT` for the transaction boundaries.

BEGIN;

ALTER TABLE customer_
   ADD COLUMN row_modified_ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp();

ALTER TABLE invoice_
   ADD COLUMN row_modified_ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp();


ALTER TABLE customer_
   ADD COLUMN row_created_ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp();

ALTER TABLE invoice_
   ADD COLUMN row_created_ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp();


CREATE OR REPLACE FUNCTION update_row_modified_function_()
RETURNS TRIGGER 
AS 
$$
BEGIN
    -- ASSUMES the table has a column named exactly "row_modified_".
    -- Fetch date-time of actual current moment from clock, rather than start of statement or start of transaction.
    NEW.row_modified_ = clock_timestamp(); 
    RETURN NEW;
END;
$$ 
language 'plpgsql';

CREATE TRIGGER row_mod_on_customer_trigger_
BEFORE UPDATE
ON customer_ 
FOR EACH ROW 
EXECUTE PROCEDURE update_row_modified_function_();

CREATE TRIGGER row_mod_on_invoice_trigger_
BEFORE UPDATE
ON invoice_ 
FOR EACH ROW 
EXECUTE PROCEDURE update_row_modified_function_();

COMMIT;