Friday, April 08, 2016

Trello is my new knowledge base

How often do you hit an issue in development and think "I know I've had this problem before, but what's the solution?"  Most days if you've been around a long time like me.  It could be "how do you create a transparent icon", or "what causes this Javascript error in an APEX page".  So you can spend a while Googling and sifting through potential solutions that you vaguely remember having seen before.

A few years ago I decided that whenever I solve an issue like this I should make a note somewhere of the issue and solution for future reference.  Initially I did that in an APEX application I built at my place of work - in fact I intended to share it with other developers, though no one else really bothered with it.  It was a kind of in-house developer forum with one user, me.

The downside of that was that I could only access the information from my place of work, and when I moved to another employer I had to leave it behind.  I considered moving it to APEX on the cloud somewhere, but by then I'd started using Trello for managing my workload on different projects, both work and personal.  Trello is really simple and effective: rather than describe it here I'll point to their own board basics page.  Also it's cloud-based so I can access it from anywhere.  I realised it would work rather nicely for my personal "knowledge base".  So I created a new board called "QandA". It looks like this:

The board consists of three lists:
  1. Solved
  2. Unsolved
  3. Help/About
The Solved list obviously contains issues that I have previously solved - this is the real "knowledge base".  The Unsolved list is stuff I'm currently solving or will need to solve.  The Help/About list is just some brief help in the unlikely event that I forgot how to use the board.

When I hit a new issue I add a card to the Unsolved list, with a title describing the problem e..g. "How to #toggle an #img #icon using just #css".  I've been using hashtags like that to aid future searching a bit (and anyway you have to have hashtags everywhere these days anyway don't you?) 

When I find out something about the solution to the problem I update the card and add the new information.  This can be a comment, a link to a web site, a picture, a Word document attachment or whatever.  Some things I add may be potential solutions that I haven't got time to check out right now.

Eventually, the problem is solved (hopefully) and then I drag and drop the card to the Solved list, and perhaps edit it to remove potential solutions I had noted but didn't work.  My knowledge repository has grown bigger.

OK now some months later I hit an issue with jQuery in Internet Explorer (of course) and I think I've solved it before.  So I open up my Trello board and filter using the relevant keywords:

In the solved list I can now see just the 4 cards that have both #ie and #jquery in them somewhere.  I see the card I'm interested in, open it up and I have the solution.  Lots of time saved!

TL;DR: use Trello, it's great!


Friday, March 04, 2016

Can't make my mind up about "Feuerstein refactoring"

When writing large PL/SQL processes I do like to try to make the code as readable as possible.  One way is to follow Steven Feuerstein's advice as exemplified here in a blog post and here in a Youtube video  to refactor the code into small chunks. I have done that, but then find I have my doubts about it.  My problem with it is that it breaks the code into small local procedures and functions which then access variables declared at a higher scope.  That seems to break a commandment of structured programming and reminds me of my early FORTRAN days and the "common block".

An example from the blog post above is varable l_required_info: this is declared in the main function can_show_information and then used within subprograms like player_can:

      FUNCTION player_can (moment_in IN VARCHAR2)
         RETURN BOOLEAN
      IS
         l_return   BOOLEAN;
      BEGIN
         l_return :=
            CASE moment_in
               ...
               WHEN qdb_competition_mgr.c_resavail_closed
               THEN
                     c_closed_or_ranked
                  OR (    info_type_in = c_see_correctness
                      AND l_required_info.players_accept_quizzes =
                             qdb_config.c_yes)
      ...
            END;

         RETURN l_return;
      END;

(I removed some of the code to focus on the bit I'm interested in).

So this function player_can takes in one parameter moment_in and returns a value, but within it accesses variables from "outside" itself, e.g. l_required_info.  I don't mind the constants like c_see_correctness, because constants are, well, constants.  But the variables disturb me. Another procedure get_required_info changes the value of this variable, also without it being passed in as a parameter:

      PROCEDURE get_required_info
      IS
      BEGIN
         OPEN required_info_cur;

         FETCH required_info_cur INTO l_required_info;

         CLOSE required_info_cur;

My structured programming head makes me want to avoid this by passing all the values that each subroutine uses explicitly as parameters.  But then it all becomes a lot more verbose of course.  I have found myself writing code in the Feuerstein style, but then feeling edgy about having to defend it when others have to maintain it! (Most people are quite happy to write a single procedure hundreds or thousands of lines long of course!)

What do you think?  Are my concerns legitimate or am I just behind the times with my "structured programming" tendency?

Wednesday, July 15, 2015

Another new APEX-based public website goes live

Another APEX public website I worked on with Northgate Public Services has just gone live:

https://londontribunals.org.uk/

This is a website to handle appeals against parking fines and other traffic/environmental fines issues by London local authorities.

It is built on APEX 4.2 using a bespoke theme that uses the Bootstrap framework.  A responsive design has been used so that the site works as well on a mobile phone as on a desktop.

Rumours that appeals against any parking tickets with my car's registration number on them are automatically approved by the system are completely unfounded.

Monday, February 02, 2015

Why won't my APEX submit buttons submit?

I hit a weird jQuery issue today that took a ridiculous amount of time to solve.  It is easy to demonstrate:

  1. Create a simple APEX page with an HTML region
  2. Create 2 buttons that submit the page with a request e.g. SUBMIT and CANCEL
  3. Run the page
So far, it works - if you press either button you can see that the page is being submitted.  

Now edit the buttons and assign them static IDs of "submit" and "cancel" respectively.  Run the page again - the buttons no longer work!  If you check for Javascript errors you will see that you are getting "Uncaught TypeError: object is not a function" (in Chrome) or similar.

Apparently this is a known issue with jQuery (see http://bugs.jquery.com/ticket/1414): 
Forms and their child elements should not use input names or ids that conflict with properties of a form, such as submit, length, or method. Name conflicts can cause confusing failures. For a complete list of rules and to check your markup for these problems, see DOMLint.

Wednesday, September 17, 2014

Ignoring outliers in aggregate function

This is another aide-memoire for myself really.  I want to calculate the average load times per page for an application from timings stored in the database, and see which pages need attention. However, the stats can be skewed by the odd exceptional load that takes much longer than a typical load for reasons that are probably irrelevant to me.

Here is a fictitious example:

create table timings (id int, timing number);

insert into timings
select rownum, case when rownum=50 then 1000 else 1 end
from dual
connect by rownum <= 100;

This example has 99 timings of 1 second plus an oddity of 1000 seconds.

A simple average gives a skewed picture:

SQL> select avg(timing) from timings;

AVG(TIMING)
-----------
      10.99

It suggests that users are waiting 11 seconds on average for a page to load, when in fact it is usually 1 second.

The analytic function NTILE(n) can solve this.  This divides the set of results into n "buckets" and then tells us which bucket a particular value falls into.  If we do that with a suitable number of buckets, say 10, we will be able to exlude the highest 10% and lowest 10% of the values:

SQL> select avg(timing) from 
  2  (select timing, ntile(10) over(order by timing) bucket
  3   from timings)
  4  where bucket between 2 and 9;

AVG(TIMING)
-----------
          1


Thursday, September 11, 2014

Why use CASE when NVL will do?

I've found that many developers are reluctant to use "new" features like CASE expressions and ANSI joins. (By new I mean: this millennium.)

But now they have started to and they get carried away.  I have seen this several times recently:

    CASE WHEN column1 IS NOT NULL THEN column1 ELSE column2 END

Before they learned to use CASE I'm sure they would have written the much simpler:

    NVL (column1, column2)

Now I now that NVL is Oracle-specific and CASE is portable, but (a) we aren't ever going to be porting our millions of lines of PL/SQL, and (b) I can guarantee they didn't do it for that reason. They have got a new hammer and now everything looks like a nail.

Saturday, August 30, 2014

Handy pre-defined Oracle collections

Note to self:

  1. SYS.DBMS_DEBUG_VC2COLL is a handy pre-defined TABLE OF VARCHAR2(1000)
  2. SYS.KU$_VCNT is TABLE OF VARCHAR2(4000)

Both are granted to public.

Thanks to Eddie Awad's blog for these.