APEX IDE for Shakespeare Programming Language (SPL)

Recently I came across an esoteric programming language called The Shakespeare Programming Language (SPL) and become rather fascinated by it.  It's big, and it's clever, but it's not terribly useful or practical.  But this year is the 400th anniversary of Shakespeare's death, which adds some relevance I suppose.

Here is an example of an SPL program taken from the SPL docs.  All it does is receive a string as input and output the same string reversed.

Outputting Input Reversedly.

Othello, a stacky man.
Lady Macbeth, who pushes him around till he pops.


                    Act I: The one and only.

                    Scene I: In the beginning, there was nothing.

[Enter Othello and Lady Macbeth]

Othello:
 You are nothing!

                    Scene II: Pushing to the very end.

Lady Macbeth:
 Open your mind! Remember yourself.

Othello:
 You are as hard as the sum of yourself and a stone wall. Am I as horrid as a flirt-gill?

Lady Macbeth:
 If not, let us return to scene II. Recall your imminent death!

Othello:
 You are as small as the difference between yourself and a hair!

                    Scene III: Once you pop, you can't stop!

Lady Macbeth:
 Recall your unhappy childhood. Speak your mind!

Othello:
 You are as vile as the sum of yourself and a toad! Are you better than nothing?

Lady Macbeth:
 If so, let us return to scene III.

                    Scene IV: The end.

[Exeunt]

I picked this one to show here because it is much shorter than the Hello World example!

I'm sure you'll agree with me that this is a language worth learning.  I decided to try it out.  There is a compiler from SPL to C available on Github, but that didn't sound much fun to me as I'm not a C programmer these days and have no particular wish to revisit it.  So I thought, why not write my own SPL to PL/SQL "compiler"?  And having done that it seemed a logically step further to build a sort of IDE for building and running SPL programs - using Oracle APEX of course, because APEX is the hammer for every nail in my life,

I am therefore proud to present my Shakespeare Programming Language IDE (I'm toying with the name "SPL Developer"):

Using this I can enter the SPL code above, click compile, and get the following generated PL/SQL code:

-- Outputting Input Reversedly.
declare
-- Othello, a stacky man.
othello t_character := t_character();
-- Lady Macbeth, who pushes him around till he pops.
lady_macbeth t_character := t_character();
-- Act I: The one and only.
begin
spl.initialise(v('P4_INPUT'));
<<acti>> null;
-- Scene I: In the beginning, there was nothing.
<<acti_scenei>> null;
-- [Enter Othello and Lady Macbeth].
-- Othello: You are nothing.
lady_macbeth.becomes( (0 ));
-- Scene II: Pushing to the very end.
<<acti_sceneii>> null;
-- Lady Macbeth: Open your mind.
othello.open_mind();
-- Remember yourself.
othello.remember();
-- Othello: You are as hard as the sum of yourself and a stone wall.
lady_macbeth.becomes( (spl.sum (lady_macbeth.val , 2 ) ));
-- Am I as horrid as a flirt-gill.
if (othello.val )= (-1 ) then
-- Lady Macbeth: If not, let us return to scene II.
null;
else
spl.log_op;
goto acti_sceneii;
end if;
-- Recall your imminent death.
othello.recall();
-- Othello: You are as small as the difference between yourself and a hair.
lady_macbeth.becomes( (spl.difference (lady_macbeth.val , 1 ) ));
-- Scene III: Once you pop, you can't stop.
<<acti_sceneiii>> null;
-- Lady Macbeth: Recall your unhappy childhood.
othello.recall();
-- Speak your mind.
othello.speak_mind();
-- Othello: You are as vile as the sum of yourself and a toad.
lady_macbeth.becomes( (spl.sum (lady_macbeth.val , -1 ) ));
-- Are you better than nothing.
if (lady_macbeth.val )> (0 ) then
-- Lady Macbeth: If so, let us return to scene III.
spl.log_op;
goto acti_sceneiii;
end if;
-- Scene IV: The end.
<<acti_sceneiv>> null;
-- [Exeunt].
end;

I've retained the original SPL as comments, followed by the PL/SQL that implements it.

I can now run it:

As you can see, I ran it with "Blog post" as input, and it returned the output "tsop golB", as expected.

I know what you're thinking: "can I have a go?"  And the answer is yes, you can! You can view and run any of the programs there, and can even add and compile one of your own if you "register" (set up a username and password).  Here it is.

I just hope Oracle can handle the traffic...

[Exeunt]

Conditional column linking in APEX

Sometimes there is a requirement to have a column in an APEX report that acts as a link to another page for some rows but not for others like this:

Here, only when a program's status is 'VALID' can we link to another page by clicking on the program name.

Until now I only knew a rather bad way of doing this, which would be to write code in the report query like:

select case when program_status='VALID'
       then '<a href="f?p=MYAPP:123:&SESSION.::&DEBUG.:123:P123_PROGRAM_NAME:' 
            || program_name || '">' || program_name || '</a>'
          else program_name
          end as program_name,

Not only is it ugly and hard to write, it has some other issues too:

1. I need to change the column's "Display As" from the default to "Standard Report Column" to prevent the link HTML being escaped and displayed literally.  And this may be frowned upon as a potential security risk.
2. If using session state protection (SSP) then I need to add a checksum to the URL by calling apex_utils.prepare_url.  Now I have a function call in the SELECT clause, which may cause performance issues.

I now have a different solution, based partly on Tyler Muth's Conditional Column Formatting post.

Here is the query for my report above:

select program_name

     , program_status

     , case when program_status != 'VALID' then 'nolink' end as class_name

  from spl_programs

The third column CLASS_NAME will be set to 'nolink' on all rows that do not have the 'VALID' status.  This column is then set to not show in the report.

In the column attributes for the PROGRAM_NAME column, I define the column link in the usual manner, except that I add class="#CLASS_NAME#" in the Link Attributes:

I can add some CSS to make "nolink" links look like plain text, and also not have the cursor change when hovering over the link:

a.nolink {

    color: #000;

    cursor: default;

}

But the user could still click on the link.  To prevent it redirecting to the other page I can change the onclick event for all "nolink" links with some "Execute when page loads" Javascript:

$('a.nolink').attr("onclick","return false;");

Now if the user clicks on the link, nothing happens.  But in theory they could disable Javascript and reinstate the default link action. Or they could view the page source or use Inspect Element to see the link's URL, and copy/paste it into the browser.  In some cases that may not matter, but if it does matter then it should be prevented, for example by adding an appropriate Authorization Scheme to the target page.

One possible issue (a colleague just reminded me) is accessibility - although it no longer looks like a link, and does nothing when clicked, to a screen reader program it is still a link, and the screen reader user may be mystified as to why it doesn't work. Perhaps instead of "return false;" it should have"alert('You cannot open this program as it is invalid');".  Or there may be a better solution?

At last APEX_PAGE.GET_URL!

I have just discovered (thanks to a tweet by @jeffreykemp) that in APEX 5.0 there is now a function called APEX_PAGE.GET_URL:

About time!  I've been using this home-made version for years:

So if I want to create a URL that redirects back to the same page with a request I can just write:

    return my_apex_utils.fp (p_request=>'MYREQUEST');

One difference is that the one I use has a parameter to decide whether or not to "prepare" the URL (add the checksum etc.)  This has been needed sometimes to ensure a URL is not prepared by the function because APEX goes ahead and prepares it again resulting in an invalid URL with two checksums.  If I recall correctly this can happen when the URL is used in a branch. Perhaps this doesn't happen in APEX 5.0 though?

APEX plugin: make tabular report responsive

I often have to build APEX applications that are responsive to the size of the screen they are running on - from desktops down to mobile phones.  While this can be achieved quite easily using a modern responsive theme, reports are often a problem.  For example, this report looks fine on a desktop:


... but gets truncated on a mobile:


Here I'm using the APEX 5.0 Universal Theme, which at least adds a horizontal scrollbar to the report, but that isn't always ideal.

I came across a solution that I liked here which uses CSS alone to reformat the report vertically on small screens - like this:


Try my demo page at http://tiny.cc/apexrr (e.g. try it on your phone).

I won't go into the details of how it works as the blog post I referenced above does that already.  However, a big attraction for me was that it does not require any Javascript, because I often have to build public website applications that need to work with Javascript disabled (yes, I know!)

Having used this technique a couple of times I decided it would be worth wrapping up into a plug-in.  After some deliberation I decided to make it a dynamic action plug-in, so that it can be added to apply the styling to a specified report region without having to manually add any CSS to the page.  However, it is an unusual dynamic action because it doesn't actually do anything dynamic - the Javscript function it performs is a dummy that does nothing.  The useful work is done by CSS that the plug-in adds to the page while rendering.

There are 3 settings for this plug-in - 2 at application level and 1 at component level:

• Application setting 1: CSS class of the report's container.  This is normally a div that surrounds the whole report, which will have its width set to 100% on small screens by the plug-in.
• Application setting 2: CSS class of report data table.  This is the table that contains just the report data (not the pagination etc.), which will be transformed by the plug-in.
• Component setting 1: Max screen width (px) affected   This governs when the transformation kicks in - default is 760 (pixels).

My thinking (currently) is that the classes will be the same from region to region within the same applicaiton, as they come from the report template, whereas the screen width at which the report needs to be transformed could vary from one region to another.

When installing the plug-in you need to set the 2 application settings for the CSS classes appropriately for the report templates you are using.

To use the plug-in you need to create a dynamic action for each report region to which it needs to be applied as follows:

• Event: Page load
• Condition: none
• Action: this plug-in
• Max screen width (px) affected: as you wish (or leave as default)
• Selection type: Region
• Region: the region to apply it to

There are some limitations and caveats to be aware of:

1. It only works on classic reports, not interactive reports.  I'm not really sure it would make sense on IRs, and their HTML is different to that of classic reports.
2. Column header sorting functionality is lost when the report is transformed for the small screen,
3. The report headings must be enclosed in a element - you may need to edit your report template and add this (in the before/after column heading sections).
4. The data table must have a class (to use in the component settings) - again, the report template can be edited if necessary.

If anyone cares to try it out and can give any feedback on how this could be improved I'd be glad to receive it - it will be available on apex-plugin.com very soon.

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!

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?