How to Tell the Database Your Web App Username

October 11, 2012

One problem I’ve run across a lot in the past is that I’ve wanted to use the username of the current user for some function in the database. Some of the more common reasons I’ve run across include:

  • Logging the username for auditing
  • App-specific permissions checks at the query level
  • Database-level per-user SLA restrictions
  • Real-time monitoring of database activity

Unfortunately, as anyone who’s worked with a web site or other application with shared connections know, that information is never available from the database unless the application explicitly provides it. A database can only tell the name of the DATABASE user that is connected, and that’s the same one for all our application users:

$user = "myappuser";
$password = "mypass";
$server = "db.company.com";
$database = "myapp";

The application can explicitly pass this value for every action, but that can end up being a world of pain, especially if your application is already pretty evolved by the time you decide it’s time to start adding some  auditing to it. You may have to go through every statement like this:

UPDATE customers SET password = '$newpass' WHERE id = $id;

with something like this:

UPDATE customers SET password = '$newpass', updated_by = $user_id WHERE id = $id;

This is the sort of thing that smells like an aspect to me. I want to add the “auditing” aspect to my already-existing code without having to rewrite everything. Fortunately, databases already provide a very simple mechanism to do that sort of thing: triggers. Unfortunately, to write one would require the database to know who my application user is without me telling it on every update…

Restating the problem, it would be great if my application could tell the database some global information about the current session, much like web apps store user session information, with every user connection. It turns out this isn’t an impossible request, at least with the two databases below!

User-defined Variables in MySQL

I spent a couple of hours recently trying to look up a solution to this kind of problem for my app which uses a MySQL database. I had a look at MySQL session variables, both static and dynamic, to see if there was something I could set in there that I could hijack for my purposes. The closest I came was the @@identity variable, which I could set to a numerical user id, but which would unfortunately be overridden on any INSERT (after all, that’s what it’s for).

It turns out the answer is much more simple, and much more powerful: user-defined variables

SET @user_id = $user_id;

The best part about this solution, besides its simplicity, is its flexibility. With this mechanism, your application can report anything it wishes to the database that should affect its overall behavior. For example, when running batch synchronization processes, rather than have to manually switch off each and every trigger, I tell the database to run in “synchronization mode” when I open the connection:

SET @synchronization = 1;

Oracle ClientID

I’m not really sure if the previous solution will work in Oracle (or in other databases) because I haven’t really tried. It turns out Oracle does provide some session variables that are actually suited to this purpose: client_id.

DBMS_SESSION.SET_IDENTIFIER($user_id);

The downside of this approach is it isn’t nearly as flexible as the approach above. But it has one ENORMOUS advantage: it is visible from the Oracle Enterprise Manager (OEM) screens (at least from 10g on). What this means is that you can view real-time (or historic) performance stats, drill down to see the top queries, zoom in to the worst, and see the actual user that is executing those queries! (more on a particularly interesting use of this feature in another blog post)

How to plug it in

It’s great that these databases offer a way to provide this information, but it doesn’t come for free. Your application still needs to explicitly set these values in the connection some time before it needs them. But, assuming you are following the DRY Principle like a good boy or girl, this shouldn’t be too much of a problem. In apps that explicitly open the connections, like a simple PHP site or a Java app that does its own connection management, this command can be executed right after the connection itself is created.

In apps that use connection pooling, this can be a little more complicated. In Java apps, I have found that the connection pool libraries often provide some sort of event callback mechanism, so the user can be set in the “beforeGetConnection” method or whatever. In more extreme cases, it may be necessary to resort to a more explicit use of an AOP framework, or explicitly get connections from a single source (e.g. static method, horror of horrors). When a framework is involved, the framework itself may require a little bit of hacking, or, in the case of Ruby or Python, some sort of monkey-patching to the framework classes.

One way or another, it may be hard to get to, but shouldn’t require more than a couple of lines of code. Definitely better than rewriting your whole app!


Everyauth support for Facebook Canvas App with Node.js

April 21, 2012

I’m working on a Facebook version of my online debates app, http://gruff.co. It’s written in Node.js and uses everyauth for authentication support. Unfortunately, it doesn’t appear to offer support for Facebook canvas apps; only for authenticating via Facebook within your own site.

I just created a fork of everyauth and added rudimentary support, so if, like me, you’ve been desperately combing the net for a solution, give it a try!

1) Add the following configs to your everyauth.facebook statements:

.canvasPath(‘/auth/facebook/canvas’)
.canvasPage(‘http://apps.facebook.com/[your-app-name]‘)

Make sure you also have specified ‘myHostname’ and ‘fields’

2) Remove your old version of everyauth

3) Change your package.json to point at my tarball version:

“everyauth”: “https://github.com/bigokro/everyauth/raw/master/everyauth.tar.gz“,

4) Run “npm install” – it should download, unpack and install my version of everyauth

5) Make sure your findOrCreateUser() knows how to look up/save the user via the oauth user data that is supplied by Facebook via the canvas page post (you should probably print out the values just to test, or look at the Chrome dev console/firebug reports.

That should do it! Note that there are a lot of TODOs in there, like passing on any querystring params that are sent to the canvas page, and verifying the signature from Facebook. I’ll probably need to do those before this can actually be added to the project, but I have already sent a pull request to get it into the official version.


OracleTC now on Github!

February 24, 2012

After years of promising, I finally managed to get around to doing a little code sanitizing, and post the code to OracleTC in a public place:

https://github.com/bigokro/OracleTC

Please do clone it, use it and improve it – and let me know about it!


The Golden Rule for Coding Standards

July 22, 2011

It can be really tedious to define coding standards to the finest detail. The following rule covers about 80% of what you really need to know:

Do unto your code as you would have others do

This rephrasing of the Golden Rule gets to the heart of what standards are all about. Whatever code you write, realize it is something that you are inflicting on other developers further down the line. Think of it as your legacy, because legacy code is what it is. Imagine yourself having to maintain the code (and you may, in fact, have to), and ask yourself if that’s code you can work with.

Also keep in mind that to some degree, you are setting the standards for all work that follows, especially considering the corollary part of this rule:

Do unto the code as others have already done

If code already exists in the project, chances are there a slew of already-established coding conventions. Before writing a single line of code in a new project, take a look around at what’s already there. Ask yourself some questions, like:

  • How is everyone formatting their code?
  • Which libraries are they already using?
  • Are there already examples of how to do what I have to do?
  • What are the overall design patterns, application layers, and so on that are already in force?

and, lastly,

  • Will I have to come up with some new conventions or solutions that others will have to follow?

By looking around, you can save yourself a LOT of time in terms of decision-making for insignificant issues (8-ball decisions), and may come up with examples that solve the majority of your work right off the bat. If you find that you need to create some new conventions, try to follow the “feel” of the code that has already been established. Otherwise, you’re good to go – just remember the Golden Rule!


Camel Hair, the A-Team and Programmer Cross-pollination

March 22, 2010

Nsaa Adinkra Symbol “He who does not know the real Nsaa buys the fake of it”

The Joys of Cross-Pollination

One subject that comes up often in my work, but in many different guises, is the benefits of “cross-pollinating” your team. It’s a no-brainer that knowledge dissemination across your team results in more consistent work, more flexibility in terms of resource assignments, and in the end, better programmers all around. The ways of disseminating that knowledge range from brown-bag lunch presentations (which generally work for 1 or 2 lunches before they run out of steam) to required code reviews to the extreme programming approach of pair programming. I know a lot of people that cringe at this technique for any number of reasons, but even in places I’ve worked where I’ve encountered unmovable resistance to this method (either because it’s a “waste” of resources, or because the programmers themselves don’t like it), I have always been able to get an exception to the rule under one circumstance: as a way to quickly transfer knowledge from one more experienced developer to another. That’s because there’s no documentation in the world that can replace having someone explain something to you AS YOU DO IT.

Cross-pollination doesn’t end at training, however. And neither does the purpose of pair programming. Common practice states that if there’s a disparity of knowledge, the least “experienced” developer is that one that should be at the keyboard to ensure that they don’t get passed over and left behind. But it also states that the pairs should be swapped with some frequency. The exact frequency is up to the team, but I generally hear anywhere from 2 hours to one day, max. The reason for this is, in part, that “more” or “less experienced” isn’t just a question of who’s older: it’s a matter of context. I, for example, have been working in Java for more than a decade. But when I work on a Rails project, PHP, or something out of my comfort zone, I have pretty much everything to learn from someone that’s been working with the technology longer than I. And when I roll onto a new project, I have to learn everything about their process, business logic, architecture, etc.

Of course, even “equal” developers have a lot to teach each other. I can’t count the number of times I was watching a presentation, doing a code review, or sitting at someone’s table when I saw their fingers hit some magic key combination that has since saved me hours of pointing and clicking. When you work that closely with others, you pick up new keyboard shortcuts, find out about cool tools, see what blogs they’re reading, and on and on. I’ve been trying out ways to replicate this kind of knowledge transfer for distributed teams using Enterprise 2.0 tools and techniques, but nothing so far can match what you get out of working on the same machine as someone else.

Cross-pollination works on other levels, too. I consider myself very lucky for the time I spent working for Sapient. That’s where I learned what it is that an architect does, and chose my personal career path in life. It’s also where I learned how teams get inspired, and how a process can work to bring people of different disciplines together. I’m also lucky to have worked at with fantastic people here in Brazil, where I learned how a process (yes, even a “heavy” process like RUP with CMMI) can work, where I learned about TDD, Continuous Integration, and more importantly, how to stay current and involved in the community at large.

In the meantime, I hear endless stories about software development shops that are stuck in the 20th-century mindset in terms of how to develop software. Everyone’s heard of TDD, but no one bothers to do it. Everyone says they do agile, but no one seems to get it right. I had a recent conversation with a good friend of mine who said that he had just been offered a promotion to be manager of the whole development team after only 6 months at a fairly successful small software and consulting company because he’d just turned around one of their most important projects from a glaring failure to a raging success. What did he do? He introduced the “radical” concepts of unit testing, continuous builds, and functional testing (and I don’t mean automated functional tests – I mean testing the software AT ALL). “In the land of the blind, the one-eyed man is king.”

The A-Team


This is all old news, but what do I make of it? The first thing that came to mind was that there’s a great opportunity here to start a company specializing in fixing up run-down software factories and development teams. There are consultants out there doing the mentoring thing, but the ones I’ve seen generally focus on a couple of weeks of training, then they cut and run. It might be interesting to put an A-Team spin on things to send in some experts that will actually work with the team to get all the right tools installed, and then make sure everyone is properly “cross-pollinated” before leaving. My ex-team at Sakonnet seems to have gotten its share of fame in the local market, since there were a number of employers hoping to snatch us up as soon as they heard we were available, so maybe I could pull us all back together again for this mission – as long as I get to be the one with the cigars.

But thinking about this a little deeper, it occurs to me that it’s a pretty sad thing that there could be such disparity between work environments. I was discussing with my “one-eyed” friend about why this happens, and I came to the conclusion that people only learn what they have to, and only what they are exposed to. There is a saying in Ghana that “He who does not know the real Nsaa [a coarse cloth made from camel hair] buys the fake of it”. And people that have NEVER worked in a place that encourages process improvements and the quest for better practices won’t know what they’re missing. Any newcomer that aspires to modernize their work area is fighting against the tide, and if they want to make any permanent changes, they have to do it quick. As soon as they themselves get comfortable in their new environment, it’s all over. Perhaps the biggest enemy to following best practices and continuous improvement is safe, comfortable, long-term employment. I’ve been reading articles on promoting what people are calling “Employment 2.0″ (when people start coining the phrase “Sex 2.0″, I’m unplugging the internet. Umm… nevermind). The idea is that by loosening ties to one single job, you increase competitiveness, you let the cream rise to the top, blah blah blah. And you INCREASE CROSS-POLLINATION.

Cross-Pollination as… a Business Model?

So there it is: it’s good to cross-pollinate your developers with each other. It can also be good to cross-pollinate them with developers from OTHER companies. This already exists: in conferences, which may be sponsored by you employer. Also, in outside professional groups, like user groups, programming Dojos and the like. But… what if we could do this as a business? What if we could combine the idea of the A-Team with employer-supported open cross-pollination? You send in a crack team of two or three senior developers to fix up a dev team’s practices. You also get a team of less-seasoned developers to help out, for CHEAP, or even for FREE. Why? Because some other company is loaning them to the cause as a form of boot camp training for
their own employees. When the short-term gig is over, they get them back, knowing that they have gained some real on-the-job experience working with the A-Team. This sort of developer loan-outs could be staggered as well. A company might loan out some key employees in advance of having the A-Team mentor the whole development department, or they may send them out on a project afterwards as a type of refresher (or because they promised to do the A-Team one favor some time in the future as payment…).

I don’t know if the idea above would really work. I’m just starting to think this through. It could be that with Employment 2.0 on the horizon, no one will want to invest in their employees anymore (in that case, it might be a good investment for you to loan out YOURSELF…). But imagine what regular company cross-pollination could do for the software and IT industry as a whole. If these assumptions are true:

  1. There is great disparity between the productivity of different software development teams
  2. This disparity is caused by a lack of awareness and experience in better practices

then it seems to follow that we would all have something to gain by getting out of our comfort zone every now and then and making yourself a junior to someone’s senior developer. I pity the fool who doesn’t. I’ll save any Hannibal quotes for when I have this plan working…


Redmine Arch Decisions 0.0.9 released

March 1, 2010

Just a quick note to let you know that version 0.0.9 of the Redmine Arch Decisions plugin has just been released. There is no new functionality in this release. Instead, I have taken the time to work on the recently-promised compatibility with Redmine 0.9.x (more specifically, I worked on “trunk”, which is currently 0.9.2). It was hell to get all the tests working (one of those cases when they are more of a pain in the butt than a help), and there were some other changes that had to be made, so I’ve given up on the idea of trying to maintain backwards compatibility. Instead, I have created a separate branch for the 0.8.4 version of Redmine (which I may or may not try to maintain).

More information about the plugin and this release can be found below:


Announcing the Arch Decisions plugin for Redmine

February 23, 2010

I’ve been silent for a long time on this blog for two important reasons:

  1. I’ve decided not to post anything unless I really have some value to add
  2. I’ve been spending my spare time working on an open source plugin for the Redmine platform

So, without further ado, I’d like to announce the release (of version 0.0.8!) of the Redmine Arch Decisions plugin! At Sakonnet, my previous gig, they were using Quickbase to track tasks, specs, and just about everything. It was a snap to add in a new feature to track “architecture” (or technical) decisions, configure notifications for collaboration, and hook them up to our issues trackers for reference and follow-up.  I wrote about this tool in a previous blog post, and I have been known to make the comment before that I couldn’t imagine working on software again without it. Well, when the time came to move on, guess what? No tool for tracking my “arch decisions”.

Fortunately, my current employers at Integritas are open to trying out new ideas, and are using the Rails-based Redmine for their issue tracking. Redmine, as with Rails in general, has a fairly usable plugin framework, and it was a great opportunity for me to get my hands dirty with RoR, so I jumped to it. Now, on the date of the release of the 8th version of my plugin (which we have been using for our projects), I feel I’m ready enough to announce it to anyone who’s looking for a way to record their technical decisions (and discuss them before they get made) without the overhead of stiff formal documents.

The following is a very brief overview of what you get in Redmine Arch Decisions 0.0.8:

Arch Decisions

Listing of Arch Decisions

The plugin includes a listing of the Arch Decisions themselves, which are currently limited to the scope of a single project. The ADs have an ID, a status, a summary, and a “Problem Description” field for more detailed information on the context of the decision. ADs currently follow a very simple workflow that isn’t being enforced, but is still useful:

  1. Not Started
  2. Under Discussion
  3. Decision Made
  4. Work Scheduled (implies that issues and/or tasks have been registered to track the implementation)
  5. Implemented (implies that all said issues and/or tasks have been completed, or at least to the satisfaction of the scope of the decision)
  6. Canceled
  7. Deprecated (implies that there’s another AD out there somewhere to replace it)

Arch Decisions also have a text field called “Resolution” that should be filled out when the status is changed to “Decision Made”. The resolution should explain what the final decision was, summarize why that decision was made, and provide any additional guidance to any developers who will be making sure the AD gets implemented.

Basic information for an Arch Decision

In addition to those basic text fields, there are also important supplemental elements embedded within the decisions that play an important role in the documentation and decision-making process (note that these are a new feature that I didn’t have in the old Quickbase version):

Factors

Factors associated with an AD

One of the most important benefits of tracking technical decisions in this way is the possibility of making all decision points and trade offs explicit. There are so many reasons why this is important :

  • You can see on one place all the reasons for which a decision was made
  • You can weigh them against one another so that no one gets fixated on a single reason
  • You can truly validate your assumptions by making them visible and discussing them individually
  • If any of these reasons change in the future, you can go back and check to see if your decision is still valid

Taking a cue from Craig Larman and others, I call these reasons “Factors”. A factor can be just about anything – a requirement, a hunch, a feature, a factoid – that can be used as a justification for a particular decision. In my personal experience, I have seen these factors tossed about with reckless and wanton abandon, littering the sacred grounds of a design discussion. The RAD plugin attempts to put a little order to this chaos by giving you one place to record this information. In general, it can be detrimental to the flow of a discussion to continuously stop to record these factors, but it can be extremely productive to let the fur fly in the heat of the moment, and then carefully pick out the key factors afterwards when you’re ready to clean house.

Factors have a status, which is important in showing which ones have been “challenged” (by marking them as “Validated” once the discussion has completed), including ones that were later shown to be incorrect assumptions (“Refuted”). There is even a text field called “Evidence” wherein the user can record exactly how they came to the conclusion regarding the validity (via external URLs, quotes from a discussion, or even a lame but honest “because Tim said so”).

Also importantly, factors can be reordered on the AD view page by simply dragging a row and placing it in the order desired. This allows you to explicitly declare which factors have a greater weight or priority, which comes in useful when a trade off must be made.

One interesting thing to note about factors is that they may have varying scopes. Some may be very specific to the Arch Decision at hand (e.g. “We will get a big bonus if we pick Strategy A!” or “The coin said ‘heads’”). Some may related to more than one AD (e.g. “The company has mandated that we use open source tools for this project”). Still others may be “global truths” that can even be applied across multiple projects (e.g. “Amazon EC2 does not support multicast between instances” (can this one be refuted yet?)). Factors can be created on their own (via the separate Factors tab), or right in the AD itself. In the latter case, they are automatically given a scope of “Arch Decision”. But this can be changed to something a little more broad. When this happens, the Factor can then be added to multiple ADs as appropriate.

Strategies

Strategies for an AD

What’s a decision without options to choose from? As with factors, my experience has been that people are good at tossing out ideas, but less good at remembering what they were later on. Or understanding anyone’s ideas but their own. So the RAD plugin also separates out a section just to track what those alternatives were that everyone proposed. Each one has a “short name”, which can be useful as reference (a little better than “wait, are you talking about the one where command comes in as a message which is then republished, or the one where you stick the command in the database and then you have a periodic task to look them up?”), plus a sightly longer summary. Then there is a detailed description for what that stratesugy would really entail.

Importantly, strategies can then be officially “rejected”, with an explanation as to why (in the future, it might be interesting to point to the key Factors). When this happens, they show up at the bottom of the list, with a big red “X” so that no one is confused as to whether or not that possibility is still being discussed (nor why it was rejected).

In some cases, you have a “there can only be one” situation, where a decision could only be considered to have been made when all the other competing strategies have been rejected. In this case, the Resolution will really just be a rewrite of the surviving strategy and its implications. In other cases, you might have multiple winners, each of which composes a part of the final resolution. I find this is especially the case when you are making decisions regarding standards – some will be rejected, while others will be accepted and adopted.

Tracking

An Issue with two related ADs

With this release, ADs can finally be associated with Redmine Issues. This is very important for tracking and governance (making sure the decision gets carried out, and that it is still followed in later implementations. It’s also true that during the course of making a decision, work has to be done on the side. Thus, the association between ADs and issues includes the “type” of relationship that an Issue bears to the AD:

  • Task – the work is a task related to making the decision (e.g. for research)
  • Proof of Concept – partial implementation projects that are required to prove whether or not a particular strategy is viable
  • Implementation – software development work intended to implement a decision (e.g. the creation of a framework according to the design specifications stipulated by the resolution)
  • Governed – implementation of the issue is expected to follow the guidelines laid out by a (possibly previously-existing) decision

Since I often work with issue trackers other than Redmine (and have been too lazy to implement a real integration), it’s also possible to define an Issue by an external URL rather than via a Redmine ID. Although the external tracker won’t have a back reference to the AD, and the AD won’t be able to report on the status of the issue, it’s certainly better than having no link at all.

Collaboration

The heart of the original idea for Arch Decisions was the ability to provide a voice to everyone involved in a decision. Ivory tower type architects would do well to take heed and use this tool. Developers don’t always like to have their instructions handed to them on a silver platter (especially when they think a bowl would be better for the soup they’re expected to eat). The RAD plugin gives developers the chance to speak up by posting comments in the Discussion sections (in fact, there’s one for each Factor and Strategy as well as the main AD itself, for those times when you need to focus on a specific subject). It also gives other project members a chance to respond, since there is a “watch” feature, and change notifications can go out via email.

In the previous incarnation of Arch Decisions, there was also a button on each issue so that a developer could raise a red flag whenever there was an implementation detail that needed to be discussed. Thus, the discussion could go both ways, so that architects are not always kept in the blue about what the developers are doing, and what they need to know. This worked very well at my last place of work. Unfortunately, I haven’t implemented this feature yet, but I’m sure it won’t be long before I do.

Final Details

Installing the plugin is very straightforward: just download Redmine and follow its basic instructions, then download the plugin, stick it in the /vendors/plugins folder, and run “rake db:migrate_plugins” to set up the database. I’ll provide a more extensive guide in another post, but hopefully that’s enough to get you started. Unfortunately, the plugin only works with version 0.8.4 of Redmine. I’d like to get it working for 0.9.x soon, so if that’s important to you, give me a holler to get off my butt.

I’ve got more tips and details to discuss about the plugin, so I’ll try to get around to that as soon as possible. Until then, let me know if you have any feedback, and I really wish you the best in your future decisions!


Follow

Get every new post delivered to your Inbox.