9 June 2016

Oracle Code QA

As Code Cop I want all my code to be clean so I keep my sanity when maintaining it. Some basic pillars that support internal code quality regardless of programming language are Coding Conventions, automated (unit) tests, Static Code Analysis and Continuous Integration. I discuss all of them in my Code Quality Assurance lecture (and its latest slides are here). A good development process covers all these and more.

Recently a colleague inherited a bunch of Oracle PL/SQL code and asked me for help. Being used to Java and many tools that help us keeping the code in shape, e.g. JUnit, Checkstyle, PMD, Jenkins, he wanted the same for his database code. While some programming language ecosystems are traditionally strong in supporting the things I mentioned earlier, some other languages seem to lack behind. Clearly there are fewer options for less used languages. But that must not stop us from applying the same rigour to our code. Let's get started!

Database Naming Conventions
First we need coding conventions because consistency is important. Unlike Java where most projects follow the Oracle conventions, there is no such thing for Oracle databases. Instead there are several, sometimes contradicting proposals and you have to put together your own set of rules. Here are some reasonable ones for schema objects:PL/SQL Coding Conventions
The Procedural Language/Structured Query Language (PL/SQL) was introduced by Oracle in 1992. It is a compiled, procedural and structured language. By these attributes it is similar to modern languages like Java or C#, and all the general advice for naming, formatting, commenting, function scope and code size apply. Even object oriented concepts like Encapsulation or Coupling are meaningful (to a certain degree). See my presentation on Clean PL/SQL for more details. Again there are no official conventions from Oracle.
  • Steven Feuerstein's Naming Conventions and Coding Standards contain a list of naming conventions for PL/SQL variables together with some guidelines and a discussion of rejected conventions. If you do not know Steven, he is probably the authority on PL/SQL programming and knows what he is talking about. He also outlines a way to check the conventions, which I really like.
  • Philip Greenspun's SQL Style contains a few rules on formatting SQL statements for better readability.
  • Trivadis' PL/SQL and SQL Coding Guidelines are a complete set of standards regarding naming, formatting, language usage and control structures. It is a very comprehensive document of almost 60 pages and looks really impressive.
How to Choose Your Own Conventions
As there is no standard, you need to roll your own. To get started I recommend reading all the resources above (and even google for some more) and get an idea what could and should be defined. Then you look at your existing database objects and source code. Usually developers follow some conventions and some percentage of the code uses similar patterns in formatting or naming. If one of the used conventions is in the limits of the different proposals above - and you like it - then start with it. (Starting from something that is already there reduces your options and the resulting conventions are less optimal, but on the other hand you have a bigger change to get the code into a consistent state, because some part of the code follows the rules. If there are no existing patterns in your code, if you are starting from scratch or if all you see is crap, you still need to define different conventions.)

Start with a small set of rules in the beginning. There should be some naming schemes, table aliases and formatting rules. If you define too many rules at once, there will be too many violations in the existing code and people will argue that adhering to the conventions is too much work. Later, when everybody got used to the rules, it is time to add more of them. You will find more specific rules during the lifetime of a project, e.g. by identifying bug patterns to be avoided in the future. Conventions need to grow. Unless you are beginning a new project and want to start with a full set of conventions, the Trivadis conventions mentioned above might be too comprehensive to start with. But they are an excellent example how a full blown conventions document looks like.

Reviewing your code, reading the provided resources and collecting the basic rules that apply and that you like should not take you more than a few hours. It is more important to start with the first version of coding conventions sooner than to start with a complete set later.

Unit Test
If you are used to JUnit, RSpec or Jasmine you will be disappointed. There is not much support for unit testing in PL/SQL.
  • Usually - if at all - developers create stored procedures that call other procedures and check the results programmatically. If these test procedures follow a common convention, e.g. raising an exception on test failure, it is possible to automate calling them from the command line or build server.
  • Another option is utPLSQL, a basic unit testing framework created by Steven Feuerstein. It works as expected, but lacks the comfort of modern unit testing frameworks. I used it to test my PL/SQL port of Gilded Rose. (Gilded Rose is a testing kata where you need to create a lot of tests. It is an excellent exercise to get a first impression of a unit testing framework.)
  • Oracle SQL Developer has some support for automated testing. Unlike utPLSQL it is driven through the user interface. Tests are created and executed through the UI of SQL Developer. A Test case is a set of input values - usually rows in one or more tables - and a call to a stored procedure. Then the updated values are compared against a set of expected values. To see this in action, check out Jeff Smith's introduction to Unit Testing Your PL/SQL with Oracle SQL Developer. It is easy to create first tests, but test definition lacks the power of a general purpose programming language. Further I do not like that test definitions are "hidden" in some SQL Developer specific tables, the Unit Test Repository. However if you are a heavy user of SQL Developer, it might still be reasonable to use it for testing, too.
  • DbFit is an extension to FitNesse, a standalone, acceptance testing framework. DbFit tests are written using tables, making some test scenarios more readable than xUnit-style tests.
  • Steven has more recommendations for unit testing PL/SQL, including Toad's Code Tester for Oracle. If you licenced the Toad Suite, it is worth checking out its testing functionality.
Unit testing is mandatory, but no single approach or framework looks superior. I believe the best approach is to evaluate the different options, using the tools you already have. Maybe create some tests for the Gilded Rose in each of the testing frameworks to see what works for you and what not.

Static Code Analysis
A vital part of code quality assurance is static code or program analysis. The source or object code is analysed without actually executing it to highlight possible coding errors. I love static analysis but have never used tools for PL/SQL myself. Steven agrees with me that we should use Lint Checkers for PL/SQL - and of course he is right. He lists some tools that add warnings besides the checks provided by Oracle.
  • A free tool is PMD for PL/SQL. PMD is my favourite code checker for Java and it works great. There are only a few rules for PL/SQL but more can be added easily. (See how I added custom rules to PMD in the past.) PMD is definitely a tool I would use first.
  • Trivadis PL/SQL Cop looks very promising. I am not sure about its licence, but it seems to be free. Rules must be checked automatically each day, e.g. in the nightly build, so tools must work from the command line. Again I do not know if PL/SQL Cop works like that. The next step would be to experiment with it and see if it can be run from the command line.
  • Another great tool is the Sonar Source PL/SQL Plugin. The plugin adds PL/SQL support to SonarQube. SonarQube is a free, open platform to manage code quality. It is widely used in the Java community. The plugin is commercial, but if you need to manage a lot of PL/SQL code I would recommend buying it nevertheless.
  • There are several other commercial tools available, e.g. ClearSQL by CONQUEST, which I did not check.
For static code analysis I follow the rule that more is better. I recommend to start with a basic tool, e.g. PMD, and keep adding tools and rules over time. In existing projects you need time to fix the violations, e.g. WHEN OTHERS THEN NULL, and starting with too many rules in the beginning creates a lot of work.

Putting It All Together
After you established conventions, added automated tests and configured some static analysis tools, it is time to put it all together and shorten the feedback loop. While you could run tests and checks manually from time to time, it would be more helpful to do so every night, or even better on each check-in/push. (Checking your code on each check-in requires you to put your DDLs and package sources under version control. While this adds some extra steps to your development workflow, I highly recommend doing so.) A tool like Jenkins or another Continuous Integration server could be used to create an empty database instance, execute your DDLs and compile all your packages. Starting with an empty database instance is important to avoid works on my machine problems. Then Jenkins should run all tests to verify that the code works as expected. The final step is to analyse your code for violations of coding convention and potential problems. Many people add more steps like generating documentation or packaging deployment bundles suitable to be deployed by the operations team's DBA.

Just Do It!
Terence Parr recommends to automate anything that you might screw up and he is right. Creating working software is hard enough, we should not bother with manual tasks, rather automate them. Further automated checks keep the quality of our software high, resulting in faster maintenance and less bugs. This leaves us more time for the interesting parts of software development - solving problem and creating solutions.

22 April 2016

Interview Franziska Sauerwein

My next "victim" for my series of interviews was Franziska "Franzi" Sauerwein. I first noticed Franzi volunteering information about developer ethics in the Software Craftsmanship Slack channel. As I kept meeting her at interesting conferences and unconferences around Europe, it was just a matter of time until she agreed to answer my interview questions. Franzi is very active on Twitter and posts to her Codurance blog from time to time. Let's see her views on work and values.

PuzzledHey, my name is Franziska Sauerwein and I'm a Software Craftswoman.
Puzzles have always amazed me and that's how I got interested in Computer Science. After completing my degree I learned that Software Development is much more about people than about sitting alone at a desk in a room and coding in isolation. I have worked as a software developer and consultant for three years in Germany before moving to London to join Codurance in the summer of 2015. My passions include Test Driven Development, Refactoring, XP techniques and high quality software development. I'm always trying to improve my skills and share knowledge. As an active member of the European Software Craftsmanship community I love to participate in unconferences and organise code retreats, hackathons, coding dojos as well as tech talks. I aim to use my skills and creativity to develop software that is reliable, easy to adapt and doing what it is supposed to do.

You said you like to discuss developer ethics. Why is that so?
As developers, we have a profound influence on our society and people's everyday lives. How we write software and what we write has an impact and with great power comes great responsibility. :)

What other topics are you concerned about?
I am a feminist, which means I believe in equal rights for all genders. I also believe in taking a critical look at my privileges as a white person with cis and class privilege. I try to inform myself on racism, ableism, hate on trans* and queer people as well as other forms of discrimination. I speak out when I witness discrimination and I try to raise awareness on these issues. I support other women, especially when they face discrimination in our industry. In my community work, I actively encourage women to take their space and men to give them their space. There are about a thousand little things that I do, from translating the Community Code of Conduct to German to recommending women to speak at conferences. I use my privileges and my extroverted personality to raise the voice of others.

What do you consider the biggest challenge of our times?
I consider the biggest challenge to show compassion towards and work with other humans in the face of a society that tells us to worry about our own safety and throw others under the bus (or out of our country).

Most people I meet are concerned about meat mass production or pollution. What could we do to engage in the topics? For example, did you take part in public protests, donate money to NGOs or sign petitions?
These are topics I used to be much more interested in a couple of years ago, when I did all the actions mentioned above. Nowadays, my passion and energy are put into issues that are closer to me and the people I care about and where I feel I can make a difference. That being said, I try to live a sustainable life as much as possible (using public transport, avoiding waste, not eating meat) but I do not consider that to be out of the ordinary. I also support people who need financial aid by donating and lending money. However, I believe financial inequality can ultimately only be solved on a political level.

Canadian Charter of Rights and FreedomsDo you think it is possible to work on "the right things" which are aligned with your values in general?
It definitely is. In fact, I believe that I can be a feminist while I am working. We need people on all levels, people that dedicate their life's work and people who are not primarily activists. And it is important to keep your activism to a level where it is sustainable, taking care of your needs as not to burn out. One can easily get overwhelmed with the number of problems out there. Allowing yourself some slack and amplifying positive changes makes it easier to deal with things.

Especially when you are in a discriminated group, there is a lot of expectation to fight and work against that. However, it should not only be your responsibility, but the responsibility of society as a whole, especially the privileged members. And it can be much harder and more frustrating to speak out and defend your position when you are affected by bad behaviour yourself.

Regarding the choice of working on the right thing: There are already many organisations out there that make conscious choices of what to work on. If you are in a position to choose, go for what you believe in. I believe in raising the bar of software development and building a community of professionals, that's why I chose to work for Codurance.

There are many decisions we take before and during a project. Which choices do you think are relevant?
I think the most likely choices we face as developers are whether to implement dark patterns, impede on people's right to their privacy, slack on security or engage in legal grey areas that ultimately cause harm. Choosing who we work with and who's voice we amplify also makes a difference. Examples like the Apple Watch not working on dark skin or health applications without period trackers show that lack of diversity in the people producing the software has a direct impact on the user. Increasing diversity in the workplace is therefore an important part of making software better for all humans. In my opinion, the largest cause of the lack of diversity in software development is due to everyday harassment, micro aggressions and systematic subtle disadvantages.

How do you think about selecting industry, customer and project based on your values?
I think everyone should choose the industry that interests them most and do what they can to make it better. If you are able to choose customer and project, choose according to whether you can leave them a better place. For example, through increasing transparency and user friendliness or providing users with protection against online harassment. You can also choose to increase profits for someone who invests some of that profit into a cause you believe in.

Do you have problems with any industries?
I do not think that it is that easy to steer clear of problematic companies, since it's not transparent who profits from what. All industries have some problematic part, mostly through exploiting people or benefiting from countries screwing each other over. I do not want to support the weapon's industry, as it profits from wars and conflicts where people get hurt.

Did you ever reject a customer or an actual project, based on your values?
I was not in that position yet.

On the other hand, what would be projects that you would love to work on?
I would like to work on projects that increase diversity and fight discrimination.

Thank you Franziska for sharing your views.

16 April 2016

Join me at GeeCON

GeeCON Conference SwagIn less than a month GeeCON is happening again. It is a great three day conference for Java and JVM developers. And you should go there! If you are from Vienna, Austria, it is especially easy and cheap to go there. But I am getting ahead of myself...

Why attending a conference?
Attending a conference has many benefits. Kevin Benore bas summarised some of them: Learning, networking, professional advancement and "Keeping the Flame Alive". Learning new things is a major reason to attend for many people. While you could watch all the content online at home, I have not met anybody who took two or three days off to do that. Also a conference gives you diverse content, sometimes you end in a talk about a topic you have no idea about. I bet that you would not watch such a talk online - why should you?. So the "change of scene" is a very important side effect of a conference. And of course there is swag - or how I call it - loot ;-). I consider swag less important and rather pay less for the conference, but the occasional shirt or mug are great mementos of past conferences.

GeeCON
GeeCON is my favourite conference and I go there every year since 2010. (I missed the first version in 2009 and I am still sad about it. But I have been to more GeeCONs that some of the organisers themselves.) I go there even when I am very busy. I am never disappointed by the content presented, see my extra short summary of GeeCON 2012. To make sure for yourself, I recommend checking out the GeeCON 2016 speaker line-up and the GeeCON Vimeo channel containing all past presentations since 2011. For this year I have the resolution to share the goodness and bring more people (from Vienna) with me, which is why I wrote this little advert.

Cost and Effort of Travelling
Some people like travelling, others do not. I am not fond of business trips and consider them a necessary evil. Kraków, the Polish city where GeeCON takes place, has an airport nearby. There are direct flights to different European airports, e.g. Berlin. From Vienna, where I live, it is even easier to reach using the night train. (I apologise for the following excessive details, but my main goal is to encourage developers from Vienna to join me.) The night train 406 leaves from Vienna Hauptbahnhof every day at 22:50 and arrives the following day at 7:00 in Kraków. When you book early, you might only pay 55 Euro for the special offer "Sparschiene". The night train is great because I can still be at work the day before the conference and I stay one night less at the hotel which saves me money again. The same is true for the return trip, train 402, which leaves Kraków each evening around 22:00.

Polish Night TrainFor the remaining two nights I usually book a cheap hotel next to the main railway station Kraków Glówny, e.g. ibis budget Krakow Stare Miasto, where I can stay for 40 Euro per night. I like the ibis because I can drop my luggage after arriving in the morning. Also one of the bus lines to the venue stops right before the ibis, reducing the hassle of travelling.

Value for Money
Depending on when I book, the total cost for a GeeCON visit (from Vienna) is between 380 and 440 Euro, excluding dinner. This is a ridiculous price for the value of a great three day conference. Because of the cheap travel, even other great community conferences like Devoxx cannot compete with GeeCON.

Training Budgets
The cheap total price enables several options.
  1. For employees now is the time to ask for your training budget. If you work for a reasonable company, there is some money for training and education for sure, however it may be small. The small cost might still make it possible to go to GeeCON on company expenses. Go, ask your manager now!
  2. In my experience money is less of a problem than the time not doing actual work. In such situations I offer to take the days off. As an employee, taking three days off is easier for me than paying 500 Euro from my wallet. This is also a fair approach as education is both the employer's and employee's obligation. So if your boss is unable to send you to GeeCON on company expenses, offer to take three days off to compensate and to show that you are serious about your continuing education. Taking the night train - while not very convenient - pays off again, because there are no extra days needed for travel.
  3. If you are independent, your training budget is exhausted or your boss is a moron, then just take the days off and pay for the whole conference on your own. I am very bad at bargaining and hate arguing with managers, so I took this option several times in the past. You might use your training budget to go to a really expensive conference like JavaOne and pay for cheaper ones yourself.
Come With Me!
As you can see - if you are from Vienna - it is easy and cheap to go to GeeCON. I would like to take you with me because I want more of us to participate and learn and have fun there. I am sad that there are only three participants from Austria attending a great international event with more than 1000 participants. So register for GeeCON now!. If you have any problems or doubts or if you need help in organising your trip, please get in touch with me.