Dropping unnamed foreign key constraints in MySQL

If you're simply amending a live database, this isn't a problem. However, when you need statically define operations with database change management tools like Liquibase or Flyway, using raw SQL change scripts, then it becomes more arduous.

The problem resides around DDLs such as ALTER TABLE requiring non-dynamic strings - you can't use subqueries or variables. This makes it difficult to use with fixed SQL files.

I chose to introduce a Stored Proc to handle the task. This example is from Liquibase:

--changeset x:1 dbms:mysql splitStatements:false

CREATE PROCEDURE drop_unnamed_fk_constraint
(
IN tableName CHAR(100),
IN columnName CHAR(100),
IN refTableName CHAR(100)
)
BEGIN
SET @sql := (SELECT CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = tableName 
AND COLUMN_NAME = columnName 
AND REFERENCED_TABLE_NAME = refTableName);

PREPARE drop_unnamed_fk_constraint_stmt FROM @sql;
EXECUTE drop_unnamed_fk_constraint_stmt;
DEALLOCATE PREPARE drop_unnamed_fk_constraint_stmt;
END 

--changeset x:2 dbms:mysql

CALL drop_unnamed_fk_constraint('Table', 'column', 'ReferencedTable');

If you're also using H2 for testing, then you can emulate the Stored Procedure with a (less than delightful) user-defined function:

--changeset x:1 dbms:h2 splitStatements:false failOnError:false

CREATE ALIAS drop_unnamed_fk_constraint AS $$
void dropConstraint(Connection conn, String tableName, String columnName, String refTableName) throws Exception {
ResultSet rs = conn.createStatement().executeQuery("SELECT CONCAT('ALTER TABLE " + tableName + " DROP FOREIGN KEY ', constraint_name) " +
        "FROM information_schema.constraints " +
        "WHERE table_name = '" + tableName.toUpperCase() + "' " +
        "AND column_list = '" + columnName.toUpperCase() + "' " +
        "AND constraint_type = 'REFERENTIAL' LIMIT 1");
    rs.next();
    conn.createStatement().executeUpdate(rs.getString(1));
}
$$;

Note that not all information_schema tables presently exist in H2, so I'm using constraints instead.

Stuck control keys under Ubuntu

Encountering a "stuck key" condition - a key that's permanently active - can be highly frustrating. A control key permanently depressed can mean that mouse events behave improperly, such as the scrollwheel controlling zoom instead of page scroll, or left mouse clicks triggering right-click menus.

I occasionally encounter this condition on an Ubuntu configuration, which I expect is an artefact of Synergy usage, but don't have the time to debug.

To resolve this frustrating issue when it occurs, I use the following alias:

alias fixkeydown='DISPLAY=:0; for i in `seq 1 256`; do xdotool keyup $i; done; echo Done'

Migrating from Rhythmbox to Clementine

Some months back, an update rendered my main Rhythmbox installation largely unusable. Changing playlists would lock the application for about 60 seconds, and ultimately Rhythmbox would end up exhausting its stack and crashing. Not good.

To save myself from having to trawl the codebase searching for the needle in a haystack, I figured I'd switch to something shiny and fresh. After some searching, I settled on Clementine; a featureful, cross-platform player, boasting an Android remote control application, integration with countless online services, good performance with large libraries, visualisations, and the like.

Porting Metadata

In order to make the switch, I needed to migrate not only my songs, but also a few key pieces of metadata I decided I couldn't do without: rating, play count, and last played date.

Rhythmbox stores its library in an xml file, and Clementine stores its library in a sqlite database, and no migration tool existed that I could see. Hence, I hacked up a quick and nasty python script to do the dirty work and enrich Clementine's database with my additional metadata.

It relies on your having first imported your music into Clementine via the Clementine user interface.

The script searches Clementine's library, correlates songs (by path) between Clementine and Rhythmbox, and where it finds matches, it enriches Clementine's metadata.

The fact that it correlates songs by path location means that the location URIs in rhythmdb.xml must match the location URIs in Clementine's database; otherwise, the script won't be able to match them up.

If you're in the same boat I was, you may find this script useful to use as-is, or as a base to build on.

Forewarning

Caveat emptor: this script was tested once, using one library, on one system, with Rhythmbox 2.99.1, and Clementine 1.2.1. That is all.  Be forewarned: expect to have to delve into the Python code to get it working, or to have to restore your Clementine configuration from backup, or for your computer to spontaneously explode in disgust, or goodness knows what else.

This script was a quick hack for my one-time use, and is not something I've lovingly crafted and polished to make "production ready".

Method

First, prepare your system:

1. Import your music library into Clementine via the Clementine user interface.
2. If necessary, make a copy of your rhythmdb.xml library (by default, located at ~/.local/share/rhythmbox/rhythmdb.xml)and perform search and replace with the tool of your choice, to clean the location URIs of any symlinks (e.g, file:///symlink/Bach/1.flac should be resolved to file:///absolute/path/to/Bach/1.flac). If you haven't used any symlinks in paths to your music files, then skip this step.
3. Take a backup of your Clementine configuration directory, just in case: cp -R ~/.config/Clementine ~/.config/Clementine-backup

Then, download the script and run it in dry-run mode:

$ wget http://content.nixnotes.co.uk/rhythmbox2clementine/rhythmbox2clementine

$ chmod +x rhythmbox2clementine

$ ./rhythmbox2clementine -d

If satisfied that things look sensible, run it in commit mode:

$ ./rhythmbox2clementine

If something went wrong and you need to restore your original Clementine configuration, simply restore the backup directory you made earlier:

$ mv ~/.config/Clementine-backup ~/.config/Clementine

Usage

$ ./rhythmbox2clementine -h

usage: rhythmbox2clementine [-h] [-d] [--rhythmdb RHYTHMDB]

                               [--clemdb CLEMDB]

Enriches a Clementine library with ratings and play counts from Rhythmbox

optional arguments:

  -h, --help           show this help message and exit

  -d, --dryrun         Don't actually commit any changes to the Clementine

                       database

  --rhythmdb RHYTHMDB  Override the default Rhythmbox library location

                       ~/.local/share/rhythmbox/rhythmdb.xml

  --clemdb CLEMDB      Override the default Clementine sqlite DB location

                       ~/.config/Clementine/clementine.db

Sample output

$ ./rhythmbox2clementine -d
Operating in DRY RUN mode
Using Rhythmbox XML DB path: /home/[USER]/.local/share/rhythmbox/rhythmdb.xml
Using Clementine sqlite DB path: /home/[USER]/.config/Clementine/clementine.db
Rhythmbox library contains 50529 songs
Clementine sqlite 3.7.17
Clementine library contains 50782 songs
DRY RUN: Not committing any changes to your Clementine library

8552 out of 50529 songs would have been enriched