Long-gun Registry Data Destruction

Posted: April 18, 2012 in Mind in the gutter

My day job has me behind a few LCD computer monitors, managing software development.  SQL is one of the computer languages I use every day.  It’s language in which humans can communicate in English syntax to a database and pose it questions, manipulate data, and change the structure (like the box or container that data would fit into) of the database tables.

My mind cycled onto this one today, and this is one of those neat (at least I think so) cases where IT and firearms cross.

Consider what is being expressed as an IT challenge by the RCMP;   now I am assuming a whole lot, but if my team had designed the tables to house the firearms data, the following hypothetical situation arrives at my desk in the form of a pointy-haired-Boss.

“Paul, we need you destroy the firearms registry data.   We need you to exclude Quebec and obviously retain the restricted and prohibited status registry.” says pointy-haired-Boss. “Roger.”, I replied as a double-click was imposed upon  (yeah, the RCMP probably uses Microsoft SQL) Enterprise Manager (version 5.5).

The following could be that SQL, given some assumptions and syntax liberties to aid the reader:

CREATE TABLE 
    restricted_firearms
    (SELECT 
        *
    FROM
        registry
    WHERE
        classification in ('prohibited','restricted')    
    )

CREATE TABLE 
    quebec
    (SELECT 
        *
    FROM
        registry
        left join licences on
            registry.pal_id = licences.pal_id and licences.prov = 'qc'
    )

-- DROP what, if any, constraints would exist :p
drop table registry;

CREATE TABLE registry
    (SELECT 
        *
    FROM
        restricted_firearms
    )        

-- REAPPLY constraints :p

DROP TABLE restricted_firearms;

So now, we can export the Quebec table to Excel, CSV, MS Access or something equally as miserable and email it to them :0  The new restricted table contains both the prohibited and restricted (future planning for when they become one) registration certificates.  Obviously the licence and related person info is persisted, and the Quebec back up only contains links to Quebec PALs.  One would assume that Quebec has a way of looking up a PAL, and all we really need to give them is the table of firearms with the PAL numbers on it, and they can link it into whatever they use.

Get all the paper, backup tapes, hard drives, or media, and distribute them to the ranges across Canada as a gift from the Government.  Members will happily dispense with them.  (seriously though… don’t use unauthorized targets at your range, and always clean-up your mess)

If the RCMP has trouble with SQL, I am more than happy to lend a hand a split this data up.   :p

I doubt it’s that easy.

 

– P

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s