Easily dumping sample data from any database

Following on from my previous post on the wonders of perl’s DBIx::Class::Schema::Loader, after you have dumped the schema using either the perl scripts provided or the dbicdump commandline tool, you can easily dump the first few rows of each table in JSON using the following very simple perl script:

use Parts::Schema;
use JSON;
my $dbic = Parts::Schema->connect('dbi:Pg:...', USER, PASSWORD);
my %data;
for my $source ($dbic->sources) {
    my $rs =
    $data{$source} = [
        map +{ $_->get_inflated_columns }, $dbic->resultset($source)->search({}, { rows => 10 })
    ]
}
print encode_json( \%data );

To dump the whole table simply remove that rows => 10 bit (although as it loads each table into memory and keeps it there before dumping I wouldn’t advise this!)

Alternatively for a NoSQL database like MongoDB:

use MongoDB;
use JSON;
my $db =MongoDB::MongoClient->new(XXX)->get_database(XXX);
my %data;
$data{$_} = [ $db->get_collection($_)->query->limit(10)->all ] for $db->collection_names;
print encode_json( \%data )

Perl’s killer feature – DBIx::Class::Schema::Loader

After I tell people I’m a programmer and they ask ‘which language’, they’re usually pretty shocked when I tell them that for server-side I use Perl out of preference. I guess most people have seen some really bad scripting in perl patched together by successive non-developers at some point in their life and assume that’s what I mean, but that’s a story for another day.

Anyway, one of the main reasons I use perl as my language of choice is because of the excellent ORM, DBIx::Class which allows you to do a lot of complex database queries and joins without having to touch SQL. Fine, you can get a decent Object-Relational Mapper (ORM) in many different languages – PHP, ruby, python, node.js for example. DBIx::Class does seem to have a lot more databases supported than your typical backend though. However the killer feature is not DBIx::Class but a separate module, DBIx::Class::Schema::Loader.

Here’s the beauty of DBIx::Class::Schema::Loader – with a typical ORM you need to define every table’s schema and relationships in code before you can start using it fully, however this module goes into the database and pulls out all of the tables, indexes, relationships and creates the full set of classes which you are then free to edit or update as you wish. The next time you run it it will pull in any database schema updates and keep the custom stuff you wrote. I’m yet to see this functionality in any other language – please point it out to me if it exists.

Now I know at this point there are many devs who like to define a database schema in code and then push it to the database so you can have versioning etc (which by the way these modules also support if you want to use them that way round). Whilst that might be OK for a new project, it’s a nightmare for an existing database or project – just think about how many days work would it be to build up ORM models of 100 tables manually. Also in my experience writing schemas in Ruby, Node.JS or even DBIx::Class is pretty nasty and repetitive – you can do a lot better using SQL CREATE TABLE statements. Another issue is that it’s very difficult to define indexes properly – perhaps you can define an index using the ORM schema definition language, but I’d wager most ORMs don’t know much about how to create a partial or functional index for example in postgres you can easily specify complicated indecies for example:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

or

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

Certainly once you go into the realms of complex geo-spatial data-types it’s impossible not to touch the database manually no matter what language or ORM you use.

An example of using DBIx::Class::Schema::Loader to connect to a database and dump all of the schema we simply have the following as a library (under dbic_lib/Parts/Schema.pm):

package Parts::Schema;
use v5.14
use base 'DBIx::Class::Schema::Loader';

our $conn_info = [ 'dbi:Pg:dbname=...', $USER, $PASS ];

sub connect { shift->SUPER::connect( @$conn_info ) }

1

And as the dumper script:

use v5.14;
use FindBin::libs qw< base=dbic_lib use=1 >;
use Parts::Schema;

Parts::Schema->loader_options({ dump_directory => "lib" });
Parts::Schema->connect;

That was easy wasn’t it? This works whether you have 2 tables or 200. So far I’ve used it with MySQL, PostgreSQL, MSSQL, Oracle, SQLite and it works brilliantly in all of these thanks to the excellent driver support in DBI which all of this is based on.

In a previous job we had an MSSQL data-warehouse that we wanted to connect to and pull out various views that defined customer lists so could email them. We used this module to refresh the schema dump on demand, reload it into the app and allow people using just a web browser to choose a view from the data warehouse, define columns or search criteria put these into a template (also created by them on the frontend) and email out. All this in about 30 lines of perl code for the database interface.

As another example just this week I was given the task of figuring out a client’s database structure with over 150 tables only about 5 of which even had a primary key. First thing – whip out the code above and get a full dump of all the tables into a perl class structure (by the way it was an oracle database – I’ve never used that brand of SQL before, wouldn’t know what command-line linux client to use and it’s a bit strange it doesn’t even have LIMIT – not a problem for DBIx::Class though). Within about 5 minutes I had a full dump of all tables including indexes, column types (and relationships if any had been defined). Within about 10 more minutes I could write a script to dump complex data based on relationships between the tables into a JSON format.

Please show me another language that has such great database-agnostic ORM support.

Update: DBIx::Class::Schema::Loader comes with a tool called dbicdump. If you just want to take a simple dump of the schema of the database above rather than writing the dump script yourself you can just run:

dbicdump Parts::Schema 'dbi:Pg:dbname=...' USER PASS

Angular Smart Table and drop-down select

The reason I wrote my previous post explaining my difficulties with selects was because I am currently using the excellent Smart Table angular module to bring some interactivity to tables in a project I’m working on. Smart Table seems to just work although I still find the st-table and st-safe-src attribute requirements a little strange.

One issue I have found is with filtering. The following does not filter properly (doesn’t filter anything), even though the option values seem to be set correctly:

<select st-search="field" st-input-event="change" st-delay=0 ng-model="dummy" class="form-control input-sm" ng-options="item.field as item.field for item in items track by item.field">
                    </select>

Instead, you have to write out the select using an ng-repeat which seems to do the trick nicely:

                    <select st-search="field" st-input-event="change" st-delay=0 ng-model="dummy" class="form-control input-sm">
                        <option value="">
                            All items
                        </option>
                        <option ng-repeat="item in items" value="{{ item.field }}">
                            {{ item.field }}
                        </option>
                    </select>

<select element problem in AngularJS

So I just spent 10 minutes trying to figure out why the following wasn’t working in AngularJS:

<select ng-options="item.name for item in items">
</select>

I spent ages checking my syntax and that the data was correct. Finally I discovered the reason – <select REQUIRES ng-model.

<select ng-model="dummy" ng-options="item.name for item in items">
</select>

works just fine…

Switching to specific KDE activites using shortcut keys

Today I’ve been playing around with an incredibly powerful feature of KDE – activities. This basically means you can switch between entire sets of applications and virtual desktops very easily. As a consultant I usually have several different clients that I work for at the same time plus a set of programs I use personally. KDE Activities allows you to separate these and switch between them very easily. The default keypress to circle around the different activities is meta-tab but I wanted to be able to switch directly to a specific one, however this doesn’t have any specific support in KDE. Fortunately with Linux and KDE being so flexible I found a solution to this from this thread from 2011. As I was implementing it I noticed a number of changes that have been made so here is an updated version of the instructions:

Firstly, get a list of activity ID’s and their human-readable names; from the console:

$ for i in $(qdbus org.kde.kactivitymanagerd /ActivityManager/Activities ListActivities); do echo -n "$i : "; qdbus org.kde.kactivitymanagerd /ActivityManager/Activities ActivityName $i; done
872ecadb-1388-41fe-89c5-4475c7fd137f : AAA
05104b7f-5df9-4ba2-80bf-0b3eca8a501d : BBB
a20e63e3-6ac6-4e0c-9f0f-2eebb3dfcc8b : CCC

Then open up the “Custom Shortcuts” program and create a new shortcut using “Edit -> New -> Global Shortcut -> D-Bus Command”. Fill out the D-Bus settings as follows:

Remote application: org.kde.kactivitymanagerd
Remote object: /ActivityManager/Activities
Function: SetCurrentActivity
Arguments: 872ecadb-1388-41fe-89c5-4475c7fd137f

And you’re done!