The Anti-Kyte

Subscribe to The Anti-Kyte feed The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 11 hours 41 min ago

ORA-06592 and the Case of the Happy Australians

Fri, 2018-01-26 14:21

Another Ashes Tour to Australia has come and gone and the home team once again hold The Urn.
For any non-cricket fans, I should probably explain.
Every four years, England sends their Men’s and Women’s Cricket Teams to Australia on a goodwill mission.
The object of the exercise is to make Australians feel good about their country as their teams inevitably triumph.

These recently concluded contests provide the theme for the illustration of the less-than-straightforward circumstance surrounding the ORA-06592 error which follows.
When encountering this error, you’ll probably see something like

ORA-06592: CASE not found while executing CASE statement

06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
           else clause.
*Action:   Add all missing cases or an else clause.

Despite this apparently definitive advice, you don’t always need to cover any possible case, or include an ELSE clause…

The buggy code

Say we have the following table and records…

create table ashes_big_inns (
    batter varchar2(50),
    team varchar2(10),
    runs number,
    not_out_ind varchar2(1))
/

insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('STEVE SMITH', 'AUSTRALIA', 239, 'N')
/

insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('ELLYSE PERRY', 'AUSTRALIA', 213, 'Y')
/

insert into ashes_big_inns( batter, team, runs, not_out_ind)
values('ALISTAIR COOK', 'ENGLAND', 244, 'Y')
/

commit;

Now, we may want to celebrate these achievements by means of the following :

set serveroutput on size unlimited
declare
    message varchar2(100);
begin
    for r_player in (select team from ashes_big_inns) loop
        case r_player.team 
            when 'AUSTRALIA' then message := q'[C'mon Aussie, C'mon !]'; 
        end case;
        dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
    end loop;
end;
/

Things start promisingly enough (especially if you’re Australian) …

C'mon Aussie, C'mon !
C'mon Aussie, C'mon !

…before falling apart like England’s middle order…

...
Error report -
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 5
06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
           else clause.
*Action:   Add all missing cases or an else clause.

According to this error then, a CASE statement must either list all possible cases or have an else clause…

An unsolved CASE

Let’s change things around a bit. This time, we’re going to put the case statement on the right hand side of an assignment…

set serveroutput on size unlimited
declare
    message varchar2(100);
begin
    for r_player in (select team from ashes_big_inns) loop
        message :=
            case r_player.team 
                when 'AUSTRALIA' then q'[C'mon Aussie, C'mon !]' 
            end;
        dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
    end loop;
end;
/

The CASE statement is still lacking a case for the team being ‘ENGLAND’ as well as an ELSE clause. So when we run it we should get the error, right?

Well, that was unexpected.

So, on the face of it, is this…

Before we get too indignant about the seemingly blatant inaccuracy in the Oracle Error message, it’s probably worth remembering that there are two distinct processing engines at work when PL/SQL is running on an Oracle Database – SQL and PL/SQL.

Reading the Manual

According to the PL/SQL documentation for the CASE Statement :

“Without the ELSE clause, if no boolean_expression has the value TRUE, the system raises the predefined exception CASE_NOT_FOUND.”

By contrast, the SQL docs for the CASE expression say that :

“If no condition is found to be true, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. ”

Let’s take a fresh look at our original effort…

set serveroutput on size unlimited
declare
    message varchar2(100);
begin
    for r_player in (select team from ashes_big_inns) loop
        case r_player.team 
            when 'AUSTRALIA' then message := q'[C'mon Aussie, C'mon !]'; 
        end case;
        dbms_output.put_line(nvl(message, 'Where did that urn go ?'));
    end loop;
exception when CASE_NOT_FOUND then
    dbms_output.put_line('I was expecting that !');
end;
/

Sure enough, when we run this we get…

From this, we can conclude that we’re running the PL/SQL CASE statement hence the raising of the pre-defined CASE_NOT_FOUND exception.
One other way of distinguishing between the PL/SQL CASE statement and the SQL CASE expression is the minor syntactical difference in their ending.
In PL/SQL you need to terminate the statement with END CASE. In SQL, you simply type END.

So, whilst the error message is correct in what it says about a CASE statement, the solution may well be to use a CASE expression instead.

Private Functions and ACCESSIBLE BY Packages in 12c

Mon, 2018-01-15 07:48

My recent post about PLS-00231 prompted an entirely reasonable question from Andrew :

“OK so the obvious question why [can’t you reference a private function in SQL] and doesn’t that defeat the objective of having it as a private function, and if so what about other ways of achieving the same goal ?”

I’ll be honest – that particular post was really just a note to self. I tend to write package members as public initially so that I can test them by calling them directly.
Once I’ve finished coding the package, I’ll then go through and make all of the helper package members private. My note was simply to remind myself that the PLS-00231 error when compiling a package usually means that I’ve referenced a function in a SQL statement and then made it private.

So, we know that a PL/SQL function can only be called in a SQL statement if it’s a schema level object or it’s definied in the package header because that’s the definition of a Public function in PL/SQL. Or at least it was…

In formulating an answer to Andrew’s question, it became apparent that the nature of Private functions have evolved a bit in 12c.

So, what I’m going to look at here is :

  • What are Private and Public package members in PL/SQL and why you might want to keep a package member private
  • How 12c language features change our definition of private and public in terms of PL/SQL objects
  • Hopefully provide some up-to-date answers for Andrew

Private and Public in the olden days

As most real-world PL/SQL functions are written within the context of a package, this is where we’ll focus our attention.

From the time that PL/SQL stored program units were introduced into Oracle, right up to and including 11g, the definition was simple.

A PL/SQL package member ( function or procedure) was public if it’s specification was declared in the package header.
Otherwise, it was private.
A private package member can only be referenced from inside it’s package.

A private package member might be used to encapsulate some functionality that is used in multiple places inside your package but not outside of it.
These “helper” functions tend to be quite common.
Another reason for using a private function would be to reduce clutter in the package signature. If your package is serving as an API to some business functionality, having few public members as entry points helps to ensure that the API is used as intended.

Of course, a private package member cannot be referenced in a SQL query, even from inside the package…

Changes in 12c and (probably) beyond

The ability to use PL/SQL constructs in SQL with clauses provided by 12c manages to take some of the certainty out of our definition of public and private. For example…

with function catchphrase return varchar2 is
    begin
        return 'I have a cunning plan which cannot fail';
    end;
select catchphrase 
from dual
/

…in 12c rewards you with :

CATCHPHRASE                                       
--------------------------------------------------
I have a cunning plan which cannot fail

Possibly more significant is the ability to create packages that are useable only by certain other stored program units using the ACCESSIBLE BY clause.

Using this new feature, we can split out our helper package members from the main package :

create or replace package baldrick_helper 
    accessible by (package baldrick)
as
    function catchphrase return varchar2;
end baldrick_helper;
/

create or replace package body baldrick_helper 
as    
    function catchphrase return varchar2
    is
    begin
        return 'I have a cunning plan which cannot fail';
    end catchphrase;
end baldrick_helper;
/

As well as reducing the size of individual packages, it should also mean that we can now reference the catchphrase function directly in a SQL statement right ? After all, it’s declared in the package header.

create or replace package baldrick 
as
    procedure cunning_plan;
end baldrick;
/

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
        select baldrick_helper.catchphrase
        into optimism
        from dual;
        
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

This compiles without error. However, when we try to run it we get :

set serveroutput on size unlimited
exec baldrick.cunning_plan;

ORA-06553: PLS-904: insufficient privilege to access object BALDRICK_HELPER
ORA-06512: at "MIKE.BALDRICK", line 5
ORA-06512: at line 1

Although the function is declared in the package header, it appears to remain private due to the use of the ACCESSIBLE BY whitelist. Therefore, if you want to reference it, you need to do it in straight PL/SQL :

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
    optimism := baldrick_helper.catchphrase;
        
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

This works as expected :

set serveroutput on size unlimited
exec baldrick.cunning_plan;

I have a cunning plan which cannot fail


PL/SQL procedure successfully completed.
Answers for Andrew

If your goal is to reference a PL/SQL package member in a SQL statement then it must be public.
In 12c this means it must be declared in the header of a package which is not defined using an ACCESSIBLE BY clause.

On the other hand, if your goal is to keep your package member private then you cannot reference it in a SQL statement.
In 12c, you do have the option of re-defining it in a with clause as mentioned earlier. However, this only works in straight SQL.
As far as code in a package is concerned, you can’t use an in-line with clause as a wrapper for the call to the private function like this…

create or replace package body baldrick as
    procedure cunning_plan is
        optimism varchar2(100);
    begin
        with function cheating return varchar2 is
        begin 
            return baldrick_helper.catchphrase;
        end;     
        begin
        select catchphrase
        into optimism
        from dual;
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

…because it’s not currently supported in PL/SQL.

Cloning around with VirtualBox

Wed, 2017-12-27 12:54

I’m in a slight trough of a week at the moment.
The excitement of seeing what Santa brought has begun to fade but I’ve yet to summon the requisite enthusiasm for seeing in the New Year.
So this post is really one of those little “Notes to self” so that I can save myself some time when next I need to spin up an Oracle database on a VirtualBox VM…

I’ve got a CentOS7 VM on which I’ve installed and configured Oracle XE.
Now, if I want to experiment on XE, I can simply create a temporary copy of this configuration by cloning it, thus saving myself the trouble of going through the basic configuration all over again.

Opening VirtualBox, we can see that I have a number of VMs already set up

To create an exact copy of ( clone) an existing VM, simply select it and either right-click and select Clone, or hit CTRL+O


…then provide the name of the new vm…

…and click Next.

NOTE – I usually don’t bother re-initialising the MAC address as I don’t run these VMs concurrently. If you are planning to do this then it’s something you may want to consider.

For the clone type, I select Full Clone as I want a completely standalone environment.

Finally, I hit the Clone button.

After a short interval, I am rewarded with an exact copy of the VM, with the new name I specified for it.

Now I’m all set to do something creative and daring in my new environment…or will be after another mince pie.

PLS-00231 – The best laid (cunning) plans and private package functions

Thu, 2017-11-30 15:02

There are times when I feel like Baldrick.
One moment I’m all boundless optimism and cunning plans and the next, I’m so confused I don’t know what my name is or where I live.
One such recent bout of existential uncertainty was caused by the error mentioned in the title of this post, or to give it it’s full name :

PLS-00231 : Function <function name> may not be used in SQL

So, from the beginning…

Let’s start with (appropriately enough) a simple package header :

create or replace package baldrick as
    procedure cunning_plan;
end baldrick;
/

No problems there, it compiles fine as you’d expect.
Now for the body…

create or replace package body baldrick as
    -- Private
    function catchphrase return varchar2 is
    begin
        return 'I have a cunning plan which cannot fail';
    end catchphrase;
    
    -- Public
    procedure cunning_plan is
        optimism varchar2(4000);
    begin
        select catchphrase
        into optimism
        from dual;
    
        dbms_output.put_line(optimism);
    end cunning_plan;
end baldrick;
/

That looks fine, right?
I mean, sure, the CATCHPHRASE function is private so it can only be referenced from inside the package but that’s not unusual, is it ?
Well, it turns out that Oracle isn’t entirely happy about this and says so at compile time…

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/9     PL/SQL: SQL Statement ignored
12/16    PL/SQL: ORA-00904: "CATCHPHRASE": invalid identifier
12/16    PLS-00231: function 'CATCHPHRASE' may not be used in SQL

After some head-scratching, I was beginning to worry that I was losing it. Maybe I should apply for that job as Village Idiot of Kensington.
Fortunately, I was saved from a career of Professional Idiocy in West London by the simple expedient of making the function public…

create or replace package baldrick as
    function catchphrase return varchar2;
    procedure cunning_plan;
end baldrick;
/

Re-creating the package header using this code, we can now see that the package body magically compiles and works without further complaint…

Cunning Plans are here again !

To discover why this happens may not require a plan more cunning than a Fox who has just been made Professor of Cunning at Oxford University but it’s my turn to cook.
So, now my code is working, I’m off to prepare the Turnip Surprise.

The Rise of AI – A Cautionary Halloween Tale

Sun, 2017-10-29 15:53

According to the hype doing the rounds at the moment, we’re all about to be replaced by Robots and/or Artificial Intelligence.
Alexa, Cortana, Siri, we already have computers that are talking to us.
Incidentally, all of these devices seem to have female voices, even the gender-neutrally named Google Assistant.
Deb reckons that this is because everyone understands that, when it comes down to it, women know best.
Whilst I wouldn’t presume to question this assertion, I must confess to being somewhat unconvinced as to the inevitability that AI is about to take over the world.
For a start, there are those automated checkouts that have been in supermarkets for several years now.
Not only are they all rather temperamental, they all have their own individual quirks.
Then there are the Sat-Navs they’re incorporating into cars. What with one thing and another I seem to be spending an increasing amount of my life arguing with other programmers’ bugs…

Some Sat-Navs are about as useful as an automated checkout for guiding you to your destination.
The one built into Deb’s car, for example, has the approximate IQ of a pebble.
She has lost count of the number of times she has been cruising along Marlborough Street whilst the Sat-Nav is convinced she is skimming across Willen Lake.
She is rapidly coming to the conclusion that she should just buy a mobile phone holder and use Google Maps. The GPS on her phone is more reliable than the car’s Sat-Nav. By more reliable, she means that at least the phone is not going to get lost pulling off the driveway.
On the plus side, a rubbish Sat-Nav is at least consistent. What happens when your car is inhabited by something that is, apparently at least, rather more competent.

The car I’m driving these days comes equipped with a number of driver aids such as Lane Assist, Parking Assist and…as it turns out…Headlight Assist.
The main User Interface to the Car’s brain is the Map screen and the Sat-Nav that sits behind it.
Due to the mandatory female voice, it has been christened Sally.
The name has stuck, even though I’ve now found the menu option that turns the voice off.

what follows is an account of a recent journey we took in the company of Sally. I believe that the events I describe support my point that SkyNet is not quite ready to murder us all in our beds.

It’s Friday evening. We’re driving down to Cornwall in the West Country for a weekend break.
We’ve never been to our destination before, but that’s OK, we gave Sally the postcode and she confidently claimed to know where it was and how long it would take us to get there.
The Autumn night has closed in and the last of the street lighting on the M5 is fast becoming a distant memory.
Suddenly, Sally pipes up, suggesting we take the next exit and join some A-road. No worries. She “knows” what she’s doing…

As instructed we turn right at the top of the slip-road. Without the motorway traffic, the darkness is really crowded in.

Alarm bells start ringing at this point. Rather than some nice, straight well-light strip of tarmac, we’re faced with a series of switch-backed corners, and hemmed in by dry-stone walls.
As we progress through the twists and turns, Sally decides to be helpful.
The full-beam headlights are designed to dip automatically to prevent dazzling drivers of oncoming vehicles.
Apparently, Sally believes that the same consideration should also be given to any houses at the side of the road. Oh, and the Moon, which has now emerged from the canopy of trees overhead.
The road has opened out and dry-stone walls have become ditches.
Sally has decided to take us across Dartmoor.

Trying hard not to think of every Werewolf movie we’ve ever seen we’re forced to slow to a pace that’s consistent with driving along an unlit road with normal dipped headlights as Sally seems to have got bored and is “playing” with the headlights. Full-beam goes on and off, seemingly at random.
Dartmoor’s natural inhabitants are out in force this evening. A large creature suddenly looming in the middle of the road turns out to be a cow. Several cows in fact. Rather relieved that the Werewolves now have an alternative nibble, we negotiate the herd and are on our way once more…taking care to avoid the sheep, foxes and ponies also wandering around.
Eventually we reach the other side of the Moor.
We pull over at the first opportunity and reach for the AA Road Atlas.

Sally is ignored for the rest of the journey.

I’ll leave you to decide whether this was an act of dumb insolence on Sally’s part in revenge for being silenced or whether I just have a Silly Sally.
I really hope she’s not reading this…

Importing an Excel Spreadsheet into an Oracle database with SQLDeveloper

Tue, 2017-10-24 14:25

It was late. In a corner of the Treasury however, a light burned brightly.
Phillip Hammond, Chancellor of the Exchequer, was still working.
“Spreadsheet Phil” was trying to formulate a plan to rectify the nation’s finances in the event that the Prime Minister was won over by the ebullient Mr Johnson and decided upon a “No Deal” Brexit.
Boris Johnson was currently based at the F.O. which, Phil considered darkly, was somewhat appropriate.
If only his predecessor had had the foresight to stick a couple of billion on Liecester City to win the league at 5000 to 1, The Country’s finances would be in much better shape.
In the midst of this gloomy reflection, he was struck by a sudden inspiration. The crowded half-hour that followed resulted in – what else – a spreadsheet.
The information therein could save The Economy. “Hmmm”, thought Phil, “this stuff is far too important to leave in Excel, I really need to put it into my Oracle database. I wonder if SQLDeveloper could help”…

The Spreadsheet

Being a cautious soul, Phil has decided to stick with something he knows quite well – the question of who is going to be the next Prime Minister of the United Kingdom of Great Britain and Northern Ireland including (for the moment at least), Scotland :

The workbook consists of two sheets – Party Leaders and Odds. At this stage, Phil just wants to upload the data in the Odds sheet.
The data in the decimal odds column is derived by means of a formula, for example :

=E3/F3

As we will see, SQLDeveloper will upload the values in cells rather than any formulas that may be present.

The observant among you will also notice that the above screenshot is taken from Libre Office Calc rather than Excel.

As I’m using Ubuntu at the moment, I don’t have a version of Excel to hand. This wouldn’t be much of a problem except for the fact that the SQLDeveloper version I’m using (4.2), doesn’t offer the option of importing data from an ODS formatted spreadsheet.
Fortunately Calc can save a workbook in numerous formats…including XLSX.
You could pretend that the British Government has come to it’s senses and is saving billions in licensing by using open source software. However, in that scenario, we’d probably be using a different RDBMS so it looks like you’re stuck with the real politik of the situation.

Anyhow, this is our spreadsheet. It is in XLSX (Excel 2003 or later) format. We want to load the data from one of it’s two Worksheets into an Oracle database table…

The target table

The DDL for the target table is :

create table next_pm(
    first_name varchar2(100) not null,
    last_name varchar2(100) not null,
    decimal_odds number,
    date_elected date
)
/

…and there is already some data in it :

select first_name, last_name, decimal_odds, 
    to_char(date_elected, 'fmddth Month YYYY') as "Elected to Parliament"
from next_pm
/

FIRST_NAME                     LAST_NAME                      DECIMAL_ODDS Elected to Parliament                         
------------------------------ ------------------------------ ------------ ----------------------------------------------
Phillip                        Hammond                                3.33 1st May 1997                                  

You’ll notice that the table does not contain the same columns as the spreadsheet does. The columns it does have in common with the spreadsheet are in a different order.
Oh, and they have names that are different from the headings in the spreadsheet.
As Phil is about to find out, all of these potential problems can be easily overcome…

The SQLDeveloper Import Data Wizard

Firing up SQLDeveloper, Phil navigates to the NEXT_PM table.
Now, he could just use the right-click menu in the Tree view on the left of the screen, but he prefers to select the table and then invoke the Actions menu…

…and selecting Import Data…, which invokes the Data Import Wizard…

When Phil selects his spreadsheet, SQLDeveloper defaults to the first Worksheet. For some reason, it also insists on defaulting the file format to Excel 95-2003 (xls)

…that’s easily changed, and Phil selects the appropriate Excel format before hitting the Worksheet drop-down to pick the appropriate worksheet

At first, SQLDeveloper isn’t too sure of about the column headings….

…but Phil just sets Skip Rows to 1 and…

Now Phil is happy with what he sees, he clicks Next

He then has a choice of Import methods. He could just opt to create an Insert script by setting the Input Method to Insert Script. Breaking the habit of a lifetime however, Phil throws caution to the wind and decides to Insert the data directly into the table. By leaving Send create script to SQL Worksheet checked, he’ll get an insert script anyway.

Time to click Next.

By default, all of the columns in the Worksheet are selected. Phil uses the left and right arrow buttons to choose only the columns that he wants to import into the table, and then the up and down arrows to specify the order …

Now, he clicks Next.

What he notices here are the red exclamation marks. SQLDeveloper is smart enough to point out that there are some issues to be sorted out…

Phil has a couple of options here. By clicking on a Source Data Column that has a red exclamation mark he can see information such as a Status explaining why the column has been marked by SQLDeveloper, the target column this data is currently pointing to in the target table, and even a sample of the data contained in the column…

He can then take the appropriate action to fix the problem. In this case, he could for example, specify that the Decimal column in the spreadsheet should be imported into the DECIMAL_ODDS table column.
However, as he’s just gone to all that trouble to get his columns in the correct order in the previous screen, he simply changes the Match By drop-down value from Name to Position :

Phil has one last little glitch to sort out, as can be seen from the Yellow exclamation mark on the DATE_ELECTED_AS_AN_MP source column.
In this case, it’s simply a question of setting an appropriate date format…

…and clicking Next

Finally, Phil has the opportunity to review his settings…

…and then, when he’s happy, hit the Finish button to import the data.

At this point, SQLDeveloper displays this reassuring message :

Now Phil can see that the data has been successfully loaded into the table whilst preserving the pre-existing record :

“Well”, thought Phil to himself, “I never knew that getting data from Excel into Oracle was so simple. Now, back to Brexit, it can’t be that hard…”

In-Line Views versus Correlated Sub-queries – tuning adventures in a Data Warehouse Report

Sat, 2017-09-30 14:22

Events have taken a worrying turn recently. I’m not talking about Kim Jong Un’s expensive new hobby, although, if his parents had bought him that kite when he was seven…
I’m not talking about the UK’s chief Brexit negotiator David Davies quoting directly from the Agile Manifesto and claiming that the British were “putting people before process” in the negotiations, although Agile as a negotiating strategy is rather…untested.
I’m not even talking about the sunny Bank Holiday Monday we had in England recently even though this may be a sign of Global Warming ( or possibly a portent for the end of days).
The fact is, we have an Ashes series coming up this winter and England still haven’t managed to find a top order that doesn’t collapse like a cheap deckchair in a light breeze.

On top of that, what started out as a relatively simple post – effectively a note to myself about using the row_number analytical function to overcome a recent performance glitch in a Data Warehouse Application – also seems to have developed an unexpected complication…

The Application

I’ve taken the liberty of stripping the application down to it’s essentials for illustrative purposes.

The application accepts a bulk upload of data into a table called INTERESTING_TIMES :
The DDL for our table looks like this :

create table interesting_times
(
    id number,
    worry varchar2(4000),
    record_type varchar2(1)
)
/

Users may then amend individual records via some kind of UI which ultimately calls :

create or replace procedure adjust_interesting_times( 
    i_id interesting_times.id%type,
    i_worry interesting_times.worry%type)
as
begin

    merge into interesting_times
        using dual
        on ( id = i_id and record_type = 'A')
        when matched then update
            set worry = i_worry
        when not matched then  insert( id, worry, record_type)
            values(i_id, i_worry, 'A');
end;
/

That’s right, when an amendment is made, the application does not perform an update. Instead, it creates a new record with a RECORD_TYPE of A(mended), preserving the O(riginal) record.
For reasons unknown, it’s apparently OK to update an existing A(mended) record.
Periodically, a report is run to list the latest version of each record in the table.

The report is as follows :

select it.id, it.worry, it.record_type
from interesting_times it
where record_type = 
    nvl((
        select 'A' 
        from interesting_times it1 
        where it1.record_type = 'A' 
        and it1.id = it.id), 'O')
order by 1
/

It’s saved in a file called worry_list.sql.

During application testing, there was no problem. The table was populated with test records…

insert into interesting_times( id, worry, record_type)
    select level as id,
        case mod(level, 3) 
            when 2 then 'North Korean Missile Exports'
            when 1 then 'Global Warming'
            else 'Brexit'
        end as worry,    
        'O' as record_type
    from dual
    connect by level <= 100
    union all
    select level,
        q'[England's top order]',
        'A'
    from dual
    where mod(level, 100) = 0
    connect by level <= 100
/

commit;

exec dbms_stats.gather_table_stats(user, 'INTERESTING_TIMES')

…and the report ran nice and quickly…

set timing on
@worry_list.sql
set timing off

...

        ID WORRY                                    R
---------- ---------------------------------------- -
        97 Global Warming                           O
        98 North Korean Missile Exports             O
        99 Brexit                                   O
       100 England's top order                      A

100 rows selected. 

Elapsed: 00:00:00.053

However, in production, data volumes are (or have grown to be) somewhat more significant…

truncate table interesting_times
/

insert into interesting_times( id, worry, record_type)
    select level as id,
        case mod(level, 3) 
            when 2 then 'North Korean Missile Exports'
            when 1 then 'Global Warming'
            else 'Brexit'
        end as worry,    
        'O' as record_type
    from dual
    connect by level <= 100000
    union all
    select level,
        q'[England's top order]',
        'A'
    from dual
    where mod(level, 100) = 0
    connect by level <= 100000
/

commit;

exec dbms_stats.gather_table_stats(user, 'INTERESTING_TIMES')

…and the application now has some performance issues…

...

        ID WORRY                                    R
---------- ---------------------------------------- -
     99995 North Korean Missile Exports             O
     99996 Brexit                                   O
     99997 Global Warming                           O
     99998 North Korean Missile Exports             O
     99999 Brexit                                   O
    100000 England's top order                      A

100000 rows selected.

Elapsed: 00:19:58.67

Yes, the runtime has mushroomed to almost 20 minutes and users are not happy.

The tkprof output makes it clear where the problem lies…

select it.id, it.worry, it.record_type
from interesting_times it
where record_type =
    nvl((
        select 'A'
        from interesting_times it1
        where it1.record_type = 'A'
        and it1.id = it.id), 'O')
order by 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668   1183.16    1191.72          0   41003158          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670   1183.17    1191.75          0   41003158          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    100000     100000     100000  SORT ORDER BY (cr=41003158 pr=0 pw=0 time=1191452315 us cost=10216576 size=24 card=1)
    100000     100000     100000   FILTER  (cr=41003158 pr=0 pw=0 time=1189848194 us)
    101000     101000     101000    TABLE ACCESS FULL INTERESTING_TIMES (cr=406 pr=0 pw=0 time=115893 us cost=116 size=2424000 card=101000)
      1992       1992       1992    TABLE ACCESS FULL INTERESTING_TIMES (cr=41002752 pr=0 pw=0 time=1189610376 us cost=113 size=7 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.01
  resmgr:cpu quantum                             16        0.13          0.70
  SQL*Net message from client                  6668        0.02          6.32
********************************************************************************

Time to rethink our Report query…

It’s row_number(), not ROWNUM

The row_number() analytical function may well come in handy here. Consider…

select id, worry, record_type,
    row_number() over( partition by id order by (case nvl( record_type, 'O') when 'A' then 1 else 2 end)) as recnum
from interesting_times
where id between 99995 and 100000
/

        ID WORRY                          R     RECNUM
---------- ------------------------------ - ----------
     99995 North Korean Missile Exports   O          1
     99996 Brexit                         O          1
     99997 Global Warming                 O          1
     99998 North Korean Missile Exports   O          1
     99999 Brexit                         O          1
    100000 England's top order            A          1
    100000 Global Warming                 O          2

7 rows selected. 

Using row_number to order records for each id, we can now eliminate the correlated sub-query from our report…

with ilv as
(
    select id, worry, record_type, 
        row_number() over( partition by id order by case nvl(record_type, 'O') when 'A' then 1 else 2 end) as recnum
    from interesting_times
)
select id, worry, record_type
from ilv
where recnum = 1
/

…and experience a stunning improvement in performance…

...
        ID WORRY                                    R
---------- ---------------------------------------- -
     99994 Global Warming			    O
     99995 North Korean Missile Exports 	    O
     99996 Brexit				    O
     99997 Global Warming			    O
     99998 North Korean Missile Exports 	    O
     99999 Brexit				    O
    100000 England's top order			    A

100000 rows selected.

Elapsed: 00:00:07.89

Yes, you are reading that right, that’s 7.89 seconds. No I didn’t forget to flush the cache between running the original report and this new version.

Now, if that was all there was to it, this would be a rather short post. However…

Hubble, Bubble, Toil And Trouble…

What happens if we just re-write the query with an in-line view and don’t bother with analytical functions at all ?

with latest_records as
(
    select id,
    max( case record_type when 'A' then 2 else 1 end) as recnum
    from interesting_times
    group by id 
)
select it.id, it.worry, it.record_type
from interesting_times it
inner join latest_records lr
    on lr.id = it.id
    and lr.recnum = case it.record_type when 'A' then 2 else 1 end
order by id
/

...
        ID WORRY                                    R
---------- ---------------------------------------- -
     99994 Global Warming			    O
     99995 North Korean Missile Exports 	    O
     99996 Brexit				    O
     99997 Global Warming			    O
     99998 North Korean Missile Exports 	    O
     99999 Brexit				    O
    100000 England's top order			    A

100000 rows selected.

Elapsed: 00:00:08.06

Yep, the performance is virtually identical to using row_number().

Looking at the respective execution plans provides a clue as to why this is.

First, the row_number query :

with ilv as
(
    select id, worry, record_type,
        row_number() over( partition by id order by case nvl(record_type, 'O') when 'A' then 1 else 2 end) as recnum
    from interesting_times
)
select id, worry, record_type
from ilv
where recnum = 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.04          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch     6668      0.58       0.63        404        406          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.59       0.69        404        406          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    100000     100000     100000  VIEW  (cr=406 pr=404 pw=0 time=403080 us cost=830 size=8080000 card=101000)
    101000     101000     101000   WINDOW SORT PUSHED RANK (cr=406 pr=404 pw=0 time=361666 us cost=830 size=2424000 card=101000)
    101000     101000     101000    TABLE ACCESS FULL INTERESTING_TIMES (cr=406 pr=404 pw=0 time=43133 us cost=113 size=2424000 card=101000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.01
  db file sequential read                         1        0.00          0.00
  db file scattered read                         19        0.01          0.02
  SQL*Net message from client                  6668        0.01          6.45
********************************************************************************

…and now the straight in-line view…

with latest_records as
(
    select id,
    max( case record_type when 'A' then 2 else 1 end) as recnum
    from interesting_times
    group by id
)
select it.id, it.worry, it.record_type
from interesting_times it
inner join latest_records lr
    on lr.id = it.id
    and lr.recnum = case it.record_type when 'A' then 2 else 1 end
order by id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.63       0.65        404        812          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.63       0.66        404        812          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    100000     100000     100000  SORT ORDER BY (cr=812 pr=404 pw=0 time=421638 us cost=852 size=32320 card=1010)
    100000     100000     100000   HASH JOIN  (cr=812 pr=404 pw=0 time=329296 us cost=851 size=32320 card=1010)
    100000     100000     100000    VIEW  (cr=406 pr=404 pw=0 time=174590 us cost=466 size=806592 card=100824)
    100000     100000     100000     HASH GROUP BY (cr=406 pr=404 pw=0 time=145304 us cost=466 size=705768 card=100824)
    101000     101000     101000      TABLE ACCESS FULL INTERESTING_TIMES (cr=406 pr=404 pw=0 time=24266 us cost=113 size=707000 card=101000)
    101000     101000     101000    TABLE ACCESS FULL INTERESTING_TIMES (cr=406 pr=0 pw=0 time=21423 us cost=113 size=2424000 card=101000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    6668        0.00          0.01
  db file sequential read                         1        0.00          0.00
  db file scattered read                         19        0.00          0.00
  asynch descriptor resize                        3        0.00          0.00
  SQL*Net message from client                  6668        0.01          6.73
********************************************************************************

Essentially, the Optimizer “materializes” the in-line-view – i.e. it creates it as a de facto temporary table.
Turns out then that it’s actually the humle in-line view that turbo charges the query rather than the nifty row_number() analytical function.

That’s one less problem I’ve got to deal with. Now to figure out England’s top order for Brisbane. Hmmm, I may need to resort to DBMS_RANDOM…

The March of IDEs – Installing Visual Studio Code on Ubuntu

Mon, 2017-08-28 06:29

When I started programming, the world was black and white. I’d write my code in Vi on Unix ( Sequent’s dynix/ptx if you want to get personal) and then run it through a compiler to find any errors. End of story, [Esc]:wq!.
Then, along came GUI IDEs with their white backgrounds and syntax colouring.
Things now seem to have come full circle as the colour schemes currently en vogue for IDEs tend to look a bit like this :

Finding a lightweight, general purpose IDE for Linux has been something of a quest for me. It’s not that there aren’t any out there, it’s just that none of them quite seemed to be exactly what I was looking for. Until now.

Look, I know that programmers tend to be rather attached to their favourite editor/IDE and this post is not an attempt to prise anyone away from their current toolset. It is simply an account of how I managed to install and configure Visual Studio Code in Ubuntu to use for Python.

Hang on, lightweight ? We’re talking about Microsoft Visual Studio, right ?
Actually, Visual Studio Code (VS Code) is a rather different beast from the Visual Studio Professional behemoth that’s used for knocking up .Net applications on Windows.

What I’m going to cover here is :

  • Installing VS Code
  • Adding the Python plug-in
  • Adding a Python linter
  • Executing a program the simple way
  • Using intellisense for code-completion
  • Debugging a program
  • Executing a program inside VS Code

If you’ve stumbled across this post in the hope of finding some information about setting up VS Code for PL/SQL, I would refer you to Morten Braten’s excellent post on the subject.

Here and now though, it’s Python all the way…

Installation

For the purposes of this post, I’m running Ubuntu 16.04 LTS as my OS.
To obtain VS Code, you could simply search the Ubuntu Software Centre for vscode and install from there.
Alternatively, you can point your browser at the Visual Studio Code download page :

In my case, I’m running Ubuntu so I need the Debian package. A 41MB download later and I’m the proud owner of…

code_1.15.0-1502309460_amd64.deb

which I can install by running …

dpkg -i code_1.15.0-1502309460_amd64.deb 

…which will lead to output similar to…

Selecting previously unselected package code.
(Reading database ... 176222 files and directories currently installed.)
Preparing to unpack code_1.15.0-1502309460_amd64.deb ...
Unpacking code (1.15.0-1502309460) ...
Setting up code (1.15.0-1502309460) ...
Processing triggers for gnome-menus (3.13.3-6ubuntu3.1) ...
Processing triggers for desktop-file-utils (0.22-1ubuntu5.1) ...
Processing triggers for bamfdaemon (0.5.3~bzr0+16.04.20160824-0ubuntu1) ...
Rebuilding /usr/share/applications/bamf-2.index...
Processing triggers for mime-support (3.59ubuntu1) ...

Now that’s done, I can see Visual Studio Code when I search in the Unity lens. Furthermore, the installation process creates a symbolic link to the Visual Studio Code executable in /usr/bin, which is in your $PATH by default. Therefore, you can start it from the command line, simply by typing :

code

Just in case things aren’t going exactly as I’ve described here, you can always refer to the full official installation instructions.

Now we’re up and running the next step is…

Adding the Python plug-in

To customize Visual Studio Code to work with a language, you’re probably going to need to add an extension. In this case, we need one for Python.

Start by clicking on the Extensions icon…

…which should display the Extensions Marketplace…

Clicking on an item in the marketplace causes further details to be displayed. I want to equip VS Code for Python so…

Click the Install button and wait for a bit. Eventually, the green install button will disappear and we’re ready to write some python…

Adding the Python Linter

OK, not quite ready. Like many IDE’s, VS Code works on the concept of workspaces which pretty much map to directories on disk.
So, I’ve created a directory under my Home in Ubuntu to hold my pythonic masterpieces…

mkdir $HOME/python_stuff

I can now open this folder from the File menu in VS Code (using the Open Folder option).

VS Code now recognizes that we’re writing a Python program.
It’s first action at this point, is to moan about the absence of a linter…

Tempting as it may be to dismiss this error and press on, it really is worth listening to VS Code.
Yes, at it’s worst, a linter can just be a way of automating your OCD – complaining about a space after a bracket here or trailing spaces there. At it’s best however, it does do the job of a compiler and point out some not-so trivial mistakes before you find them yourself by running the code and wading back through the error stack.

So, click on the Install pylint option…

At this point you may get one or more of the following errors…

If you’re using a vanilla install of Ubuntu, or most other distros for that matter, it’s likely that you haven’t got the python package manager, Pip, installed.
This will cause VS Code to display something like this in the terminal window :

/usr/bin/python -m pip install pylint
/usr/bin/python: No module named pip

We can fix this by installing pip :

sudo apt-get -y install python-pip

Once that’s done, you now need to re-execute the command VS Code was trying to run.

Just a pause before we get there though. If you did have pip installed, you would be likely to run across a different error.
This is likely to be a permissions issue. Simply re-executing the command as sudo should resolve this.

sudo /usr/bin/python -m pip install pylints

Anyhow, now we’ve got the linter installed we can now edit message.py. Let’s keep things simple…

print('Ooohh, this is new !')

As you start typing Intellisense kicks in offering completion options.
Once you save the file, you can click on the problems tab and see that the linter is at it’s most obsessive/compulsive…

Picky, picky, picky

Among other things, you’ll notice that the linter is complaining about the brackets in the print statement. This is because we’re using the distros default python executable – i.e. 2.7.6.
You can persuade VS Code to use Python 3 should you be so inclined. Details for doing this can be found in the articles I’ve linked to at the end of the post.

After making a few adjustments to my code…

''' Docstring to keep the linter happy '''
print 'Ooohh, this is new !'

the linter is now content…

NOTE We will come on to a more “integrated” way of running a program in your Integrated Development Environment later. For now though, to run the program, right-click in the Editor and select Run Python File in Terminal from the pop-up menu …

…which happens in the integrated Terminal tab :

That’s all OK, I suppose, but how about something a bit more substantial as an example ?

An example that’s a bit more (intelli)sensible

Here’s something that’s a bit more of a challenge for VS Code, a program to provide details of the platform it’s running on.
We’ll be using the platform library, which should give Intellisense a bit more of a workout.
We’ll also be setting some variables, which could prove useful when we come to test VS Code’s debug features.

The finished code will be saved in a file called current_machine.py and will look something like this :

''' Display details of the current machine '''
import platform

def main():

    machine_name = platform.node()
    os_type = platform.system()
   
    print 'Host - ' + machine_name + ' running on ' + os_type

    if platform.system() == 'Linux':
        print platform.linux_distribution()

if __name__ == '__main__':
    main()

As you start to reference the platform package, Intellisense kicks in with an accurate list of options :

Right, I’ve finished writing my program and I’m saving it. Unfortunately there’s a typo in here ( I’ve not indented the print statement)

Fortunately, by going to the Problems tab, I can see that the Linter has caught this :

Picky, picky…actually, you’ve got a point.

Once I’ve made the correction, and added the newline at the end of the program, the linter still is not entirely happy :

However, this is a warning rather than an error, so I’m going to ignore it and press on…

Running in Debug

Clicking on the Debug button opens up the debugging pane :

I can add breakpoints simply by clicking in the gutter to the left of the line numbers.

In my case I have one at line 7, just before the assignment of os_type and one at line 11, before the if statement.

You can see the details of these breakpoints in the Breakpoints section at the bottom of the Debug Pane :

Now, I want to add a watch for the os_type variable.
If I hover the mouse over the Watch title bar, I get a “+” icon…

Click on this, I can enter the name of the variable I want to watch…

Note that, at this stage, we have no configuration defined and a little red dot on the settings icon at the top of the debug pane.

If we click on this, a file called launch.json appears…

Behind the scenes, this file has been generated in the root directory of the project under the .vscode hidden directory.

As a result, we now have a Python config we can use for our debugging session.

Now we have the Python config selected, hit the green run button next to the config name…

Using the debug button palette that appears, click the run button which should take us to the first breakpoint…

If I then hit the Step Over button, I can see the new value of os_type

If I hit it again, the output of the print statement shows up in the Debug Console :

At the end of the program execution, it all looks like this :

Adding a task to run Python natively in VS Code

Remember, I said a little while ago about enabling VS Code to run our Python programs from inside the tool itself ?

In the Tasks menu, select Configure Tasks

This should give you a drop-down list of option. Select Others

For now, we’re going to keep things simple and just replace the contents of our new tasks.json so that it looks like this :

{
    // See https://go.microsoft.com/fwlink/?LinkId=733558
    // for the documentation about the tasks.json format
    "version": "0.1.0",
    "command": "python",
    "isShellCommand": true,
    "showOutput": "always",
    "args": ["${file}"]
}

Save this file. You can now see it in the same location as launch.json which we created earlier for the debugger :

 ls -1 ~/python_stuff/.vscode
launch.json
tasks.json

We can now execute the program from within VS Code. From the Tasks menu select Run Task…
Our newly configured python task will appear in the drop-down :

Select the task and the program will execute. We can see the output in the Output Pane :

Of course, this isn’t the end of the story. There’s far more that you can do to make yourself comfortable in VS Code.

This article from Linux on Azure and this one by Doug MaHugh, both address the question of what you need to do to use the Python 3 interpreter.

Hopefully though, this is enough to get you started.

Using Edition Based Redefinition for Rolling Back Stored Program Unit Changes

Thu, 2017-08-10 15:14

We had a few days of warm, sunny weather in Milton Keynes recently and this induced Deb and I to purchase a Garden Umberella to provide some shade.
After a lifetime of Great British Summers we should have known better. The sun hasn’t been seen since.
As for the umbrella ? Well that does still serve a purpose – it keeps the rain off.

Rather like an umbrella Oracle’s Edition Based Redefinition feature can be utilized for purposes other than those for which it was designed.
Introducted in Oracle Database 11gR2, Edition Based Redefinition (EBR to it’s friends) is a mechanism for facilitating zero-downtime releases of application code.
It achieves this by separating the deployment of code to the database and that code being made visible in the application.

To fully retro-fit EBR to an application, you would need to create special views – Editioning Views – for each application table and then ensure that any application code referenced those views and not the underlying tables.
Even if you do have a full automated test suite to perform your regression tests, this is likely to be a major undertaking.
The other aspect of EBR, one which is of interest here, is the way it allows you to have multiple versions of the same stored program unit in the database concurrently.

Generally speaking, as a database application matures, the changes made to it tend to be in the code rather more than in the table structure.
So, rather than diving feet-first into a full EBR deployment, what I’m going to look at here is how we could use EBR to:

  • decouple the deployment and release of stored program units
  • speed up the process of rolling back the release of multiple stored program unit changes
  • create a simple mechanism to roll back individual stored program unit changes

There’s a very good introductory article to EBR on OracleBase.
Whilst you’re here though, forget any Cross-Edition Trigger or Editioning View complexity and let’s dive into…

Fun and Games when releasing Stored Program Units

As I’ve observed before, deploying a new version of a PL/SQL program unit is destructive in nature. By default, the old version of the program is overwritten by the new version and is unrecoverable from within the database.
This can be problematic, especially on those occasions when you discover that your source control repository doesn’t contain what you thought it did.

Having the safety net of the old version stored in the database, ready to be restored should the need arise, is not something to be sniffed at.
Incidentally, Connor Macdonald has his own roll-you-own method for backing up PL/SQL source.

Before we get into how EBR can help with this, we need to do a bit of configuration…

Setting up Editions

From 11gR2 onward, any Oracle database will have at least one Edition…

When you connect to the database, you can specify an Edition to connect to. By default this is the current Database Edition.
To start with, when you’re connected, both the current edition and session edition will be ORA$BASE :

select sys_context('userenv', 'current_edition_name') as default_edition,
    sys_context('userenv', 'session_edition_name') as session_edition
from dual;

However, by default, it does not appear that any database objects are associated with the ORA$BASE edition.
Taking the HR schema as an example :

select object_name, object_type, edition_name
from dba_objects_ae
where owner = 'HR'
and object_type != 'INDEX'
order by 1,2
/

When we execute this query, we get :

That’s because, at this point, HR is blissfully unaware of any Editions. However, if we enable editions for this user…

alter user hr enable editions
/

…and re-execute the query, we can see that things have changed a bit…

The Editionable objects in the schema ( Procedures, Triggers and the View) are now associated with the ORA$BASE edition.

The scope of an Edition is the database ( or the PDB if you’re on 12c). To demonstrate this, let’s say we have another schema – called EDDY :

create user eddy identified by eddy
/

alter user eddy temporary tablespace temp
/

alter user eddy default tablespace users
/

alter user eddy quota unlimited on users
/

grant create session, create table, create procedure to eddy
/

alter user eddy enable editions
/

Eddy is a bit of an ‘ed-banger and the schema contains the following…

create table rock_classics
(
    artist varchar2(100),
    track_name varchar2(100)
)
/

create or replace package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
end i_love_rock_n_roll;
/

create or replace package body i_love_rock_n_roll as
    function eddy_the_ed return varchar2 is
    begin
        return 'Run to the Hills !';
    end eddy_the_ed;
end i_love_rock_n_roll;
/

At this stage, these objects have the Edition you would expect. This time, we can query the USER_ version of the OBJECT_AE view whilst connected as EDDY…

select object_name, object_type, edition_name
from user_objects_ae
order by 2,1
/

I want to make some changes to the code in the EDDY application. In order to preserve the “old” code, as well as making deployment a fair bit easier, I need a new edition…

Using a New Edition

First off, as a user with the CREATE EDITION privilege…

create edition deep_purple
/

We can see that the new Edition has been created with ORA$BASE as it’s parent…

At present ( i.e. 12cR2), an Edition can have only one child and a maximum of one parent. Every Edition other than ORA$BASE must have a parent.
Therefore, it’s probably helpful to think of Editions as release labels rather than branches.

At this point, whilst we now have two editions in the database, it’s only possible for EDDY to use one of them.
If EDDY attempts to switch to the new Edition…

alter session set edition = deep_purple
/

…we get…

In order for EDDY to be able to use the new Edition, we need to grant it…

grant use on edition deep_purple to eddy
/

Now Eddy can see the new edition as well as the existing one :

alter session set edition = deep_purple
/
select property_value as default_edition,
    sys_context('userenv', 'session_edition_name') as session_edition
from database_properties
where property_name = 'DEFAULT_EDITION'
/

Now we have access to the new Edition, we’re going to make some changes to the application code.
First of all, we want to add a function to the package :

create or replace package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
    function motor_ed return varchar2;
end i_love_rock_n_roll;
/

create or replace package body i_love_rock_n_roll as
    function eddy_the_ed return varchar2 is
    begin
        return 'Run to the Hills !';
    end eddy_the_ed;
    function motor_ed return varchar2
    is
    begin
        return 'Sunrise, wrong side of another day';
    end motor_ed;
end i_love_rock_n_roll;
/

We’re also going to create a new standalone function :

create or replace function for_those_about_to_rock
	return varchar2 as
begin
	return 'We salute you !';
end for_those_about_to_rock;
/

Looking at how these changes have affected the Editions with which these objects are associated with is revealing :

select object_name, object_type, edition_name
from user_objects_ae
order by 1,2
/

OBJECT_NAME              OBJECT_TYPE   EDITION_NAME
-----------              -----------   ------------
FOR_THOSE_ABOUT_TO_ROCK  FUNCTION      DEEP_PURPLE
I_LOVE_ROCK_N_ROLL       PACKAGE       ORA$BASE
I_LOVE_ROCK_N_ROLL       PACKAGE       DEEP_PURPLE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY  ORA$BASE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY  DEEP_PURPLE
ROCK_CLASSICS            TABLE

The new function, for_those_about_to_rock, is assigned to the current session edition as we would expect. However, it appears that the i_love_rock_n_roll package is now assigned to both versions.
That’s not right, surely ?

Let’s do a quick check…

select i_love_rock_n_roll.motor_ed
from dual
/

MOTOR_ED
--------
Sunrise, wrong side of another day

So, we can see the new package function.
However, if we now switch to the other Edition…

alter session set edition = ORA$BASE
/

Session altered.

…and try to invoke the standalone function we just created…

select i_love_rock_n_roll.motor_ed
from dual
/

Error starting at line : 1 in command -
select i_love_rock_n_roll.motor_ed
from dual

Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "I_LOVE_ROCK_N_ROLL"."MOTOR_ED": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

However, we can still see the original package…

select i_love_rock_n_roll.eddy_the_ed
from dual
/

EDDY_THE_ED
-----------
Run to the Hills !

Where it gets really interesting – for our current purposes at least, is that we can see the source code for both versions of the package in the USER_SOURCE_AE view.
For the original Package Header :

select text
from user_source_ae
where type = 'PACKAGE'
and name = 'I_LOVE_ROCK_N_ROLL'
and edition_name = 'ORA$BASE'
order by line
/

…we get …

TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
end i_love_rock_n_roll;

…but we can also get the new version from the same view…

select text
from user_source_ae
where type = 'PACKAGE'
and name = 'I_LOVE_ROCK_N_ROLL'
and edition_name = 'DEEP_PURPLE'
order by line
/

…returns…

TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
package i_love_rock_n_roll as
	function eddy_the_ed return varchar2;
    function motor_ed return varchar2;
end i_love_rock_n_roll;

One other point to note is that you can grant privileges on an object that only exists in your “new” edition…

SQL> grant execute on for_those_about_to_rock to hr;

Grant succeeded.

…but when connected as that user, the object will not be visible…

select eddy.for_those_about_to_rock from dual;

Error starting at line : 1 in command -
select eddy.for_those_about_to_rock from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "EDDY"."FOR_THOSE_ABOUT_TO_ROCK": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

…nor will the grantee be able to see the Edition if they do not otherwise have privileges to do so…

alter session set edition = deep_purple;

Error starting at line : 1 in command -
alter session set edition = deep_purple
Error report -
ORA-38802: edition does not exist
38802. 00000 -  "edition does not exist"
*Cause:    This error occurred because a reference was made to an edition that
           does not exist or that the current user does not have privileges on.
*Action:   Check the edition name and verify that the current user has
           appropriate privileges on the edition.
Releasing code using Editions

As we can see, Editions allow us to separate the deployment of code from the actual release.
We’ve already deployed our application changes but they are only visible to eddy at the moment.
NOTE – as I said at the start, we’re only using EBR for releasing stored program units. If we had any table DDL then we’d need to deal with that separately from EBR in these particular circumstances.

Anyhow, once we’re sure that all is well, we just need to “release” the code from the DEEP_PURPLE edition as follows :

alter database default edition = deep_purple
/

Now when we connect as hr…

select sys_context('userenv', 'session_edition_name')
from dual
/

SYS_CONTEXT('USERENV','SESSION_EDITION_NAME')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEEP_PURPLE

…and the new function is now accessible…

select eddy.for_those_about_to_rock
from dual
/

FOR_THOSE_ABOUT_TO_ROCK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
We salute you !                                                                                                                                                        

Note that, whilst the Editionable objects in the HR schema itself have not directly inherited the new Edition…

select object_name, object_type, edition_name
from user_objects_ae
where object_type in ('PROCEDURE', 'TRIGGER', 'VIEW')
/

OBJECT_NAME               OBJECT_TYPE         EDITION_NAME
------------------------- ------------------- ------------------------------
UPDATE_JOB_HISTORY        TRIGGER             ORA$BASE
ADD_JOB_HISTORY           PROCEDURE           ORA$BASE
SECURE_EMPLOYEES          TRIGGER             ORA$BASE
SECURE_DML                PROCEDURE           ORA$BASE
EMP_DETAILS_VIEW          VIEW                ORA$BASE                      

…they are still usable now that we’ve migrated to the DEEP_PURPLE edition…

select first_name, last_name
from emp_details_view
where department_id = 60
/

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Alexander            Hunold
Bruce                Ernst
David                Austin
Valli                Pataballa
Diana                Lorentz                  

Rolling back the entire release

If we need to rollback all of the code changes we’ve made EBR makes this process very simple.

Remember, the objects owned by EDDY in the DEEP_PURPLE Edition are :

select object_name, object_type
from dba_objects
where owner = 'EDDY'
order by 2,1
/

OBJECT_NAME              OBJECT_TYPE
-----------              -----------
FOR_THOSE_ABOUT_TO_ROCK  FUNCTION
I_LOVE_ROCK_N_ROLL       PACKAGE
I_LOVE_ROCK_N_ROLL       PACKAGE BODY
ROCK_CLASSICS            TABLE         

…and the package members are…

Now, to rollback all of the application changes associated with the DEEP_PURPLE Edition, we simply need to run…

alter database default edition = ora$base
/

We can see that this has had the desired effect

select object_name, object_type
from dba_objects
where owner = 'EDDY'
order by 2,1
/

OBJECT_NAME         OBJECT_TYPE
-----------         -----------
I_LOVE_ROCK_N_ROLL  PACKAGE
I_LOVE_ROCK_N_ROLL  PACKAGE BODY
ROCK_CLASSICS       TABLE

The function has disappeared, along with the additional package member…

Well, that’s nice and easy, but how could we use EBR to rollback a single change rather than the entire release ?

Rolling back a single change

To demonstrate this, we need to set the current Edition back to DEEP_PURPLE…

alter database default edition = deep_purple
/

Remember that, where relevant, EBR ensures that a copy of an object’s source code for previous Editions is kept in the Data Dictionary.
We can use this stored code to restore these versions to the current Edition.
NOTE – the owner of this next procedure will need the ALTER ANY PROCEDURE privilege :

create or replace procedure restore_version
(
    i_owner dba_source_ae.owner%type,
    i_name dba_source_ae.name%type,
    i_type dba_source_ae.name%type,
    i_source_edition dba_source_ae.edition_name%type,
    i_target_edition dba_source_ae.edition_name%type
)
is
--
-- Simple procedure to demonstrate restoring a given Edition's version
-- of a stored program unit.
--
    -- The DDL we execute will complete the current transaction so...
    pragma autonomous_transaction;

    rb_source clob;
begin
    if i_owner is null or i_name is null or i_type is null
        or i_source_edition is null or i_target_edition is null
    then
        raise_application_error(-20000, 'Values for all parameters must be supplied');
    end if;

    -- Make sure our session is in the target edition. If not then error.
    if upper(i_target_edition) != upper(sys_context('userenv', 'session_edition_name')) then
        raise_application_error(-20001, 'Session must be in the target edition');
    end if;

    for r_code in
    (
        select line,text
        from dba_source_ae
        where owner = upper(i_owner)
        and name = upper(i_name)
        and type = upper(i_type)
        and edition_name = upper(i_source_edition)
        order by line
    )
    loop
        if r_code.line = 1 then
            rb_source := 'create or replace '
                ||replace(lower(r_code.text), lower(i_type)||' ', i_type||' '||i_owner||'.');
        else
            rb_source := rb_source||r_code.text;
        end if;
    end loop;

    if nvl(length(rb_source),0) = 0 then
        raise_application_error(-20002, 'Object source not found');
    end if;    

    -- execute the ddl to restore the object
    execute immediate rb_source;

end restore_version;
/

In the current example we have the Package header and Package body of EDDY.I_LOVE_ROCK_N_ROLL in both the ORA$BASE and DEEP_PURPLE Editions.
If we want to reverse these changes but leave the rest of the release unaffected, we can simply invoke this procedure…

begin
    restore_version('EDDY', 'I_LOVE_ROCK_N_ROLL', 'PACKAGE', 'ORA$BASE', 'DEEP_PURPLE');
    restore_version('EDDY', 'I_LOVE_ROCK_N_ROLL', 'PACKAGE BODY', 'ORA$BASE', 'DEEP_PURPLE');
end;
/

We can now see that the original package has been restored and is available in the DEEP_PURPLE Edition, along with the other code from the release. However, the package function we’ve removed isn’t :

As it stands, this would is a one-time operation on as we’re effectively restoring the old version by creating it in the new Edition. At that point the stored program units are identical in both Editions.
The obvious solution would be to change the session edition programatically in the procedure. Unfortunately, attempts to do so run into :

ORA-38815: ALTER SESSION SET EDITION must be a top-level SQL statement

Of course, you could issue the ALTER SESSION commands in the script you’re using to call the procedure. However, you would then also need to make a copy of the current Edition code before restoring the old version and it all gets fairly involved.

Conclusion

Whilst all of this isn’t quite using EBR for it’s intended purpose, it does offer a couple of advantages over the more traditional method of releasing stored program unit changes.
First of all, you can separate the deployment of code into your production environment and making it visible to users.
Secondly, releasing the code becomes a single ALTER DATABASE statement, as does rolling back those changes.
Finally, it is possible to quickly revert individual stored program units should the need become evident once the release has been completed.
All of this functionality becomes available without you having to write much code.
The downside is that a reversion of an individual program unit is a one-time operation unless you write some custom code around this, which is what we were trying to get away from to start with.
Additionally, without implementing any Editioning Views, you will still have to manage structural changes to tables in the same way as before.

The weather forecast is sunny for this coming weekend. Unfortunately, that means I’ll have to mow the lawn rather than sit under the umbrella. Honestly, I’m sure astro-turf can’t be that expensive…


Filed under: Oracle, PL/SQL Tagged: 'current_edition_name'), 'session_edition_name'), alter database default edition, alter session set edition, alter user enable editions, create edition, database default edition, dba_objects_ae, dba_source_ae, Edition Based Redefinition, execute immediate, ORA-38802: edition does not exist, ORA-38815: ALTER SESSION SET EDITION must be a top-level SQL statement, session edition, sys_context('userenv', user_objects_ae, user_source_ae

Keyboard not working in Citrix Receiver for Linux – a workaround

Sat, 2017-07-08 14:47

In technological terms, this is an amazing time to be alive.
In many ways, the advances in computing over the last 20-odd years have changed the way we live.
The specific advance that concerns me in this post is the ability to securely and remotely connect from my computer at home, to the computer in the office.
These days, remote working of this nature often requires the Citrix Receiver to be installed on the client machine – i.e. the one I’m using at home.
In my case, this machine is almost certainly running a Linux OS.
This shouldn’t be a problem. After all, the Citrix Receiver is available for Linux. However, as with any application available on multiple platforms, any bugs may be specific to an individual platform.
I was reminded of this recently. Whilst my Windows and Mac using colleagues were able to use the Citrix Receiver with no problems, I found the lack of a working keyboard when connecting to my work machine something of a handicap.
What follows is a quick overview of the symptoms I experienced, together with the diagnosis of the issue. Then I go through the workaround – i.e. uninstalling the latest version of the Receiver and installing the previous version in it’s place.

Version and OS specifics

I’ve replicated what follows on both Ubuntu 16.04 ( the current LTS version) and Linux Mint 17.3 (Cinnamon desktop). Whilst these are both Debian based distros using the .deb package, I believe that the issue in question applies to the Receiver for any Linux distro.
Both of the machines I worked on were built on the x86_64 architecture (essentially any 64-bit Intel or AMD processor).
The Receiver version in which the problem was encountered is 13.5.

The symptoms

The problem I encountered was that, once I had polluted my lovely Linux desktop by connecting to to my Windows 7 workstation via the Receiver, the keyboard was unresponsive in the Receiver Window.
The mouse still works. If you switch out of the Receiver window, the keyboard still works.
Switching between Window and Full-Screen view in the Receiver – which sometimes solves intermittent responsiveness issues – does not resolve this particular problem.

The Diagnosis

Whilst initially, I suspected this could be some kind of hardware or driver issue specific to my machine, the fact that I was able to replicate this on multiple PCs using multiple Linux Distros lead me to do some digging.
This lead me to this bug report on the Citrix site.

Good news then. I don’t have to delve into the murky world of drivers. Bad news, it looks like I’m going to have to schlep into the office until Citrix get around to fixing the bug.Or maybe not…

The Workaround

Faced with the prospect of being nose-to-armpit with a bunch of strangers on The Northern Line, I decided that installing the previous version of the Receiver was worth a go.

***Spoiler Alert*** – it worked.

The steps I took to uninstall and re-install The Receiver are as follows…

First of all, verify the version of The Receiver that’s installed :

dpkg -l icaclient

If it’s installed you should see something like :

Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                          Version                     Architecture                Description
+++-=============================================-===========================-===========================-===============================================================================================
ii  icaclient                                     13.5.0.10185126             amd64                       Citrix Receiver for Linux

Remember, the version with the problem is 13.5 so, if you have that version installed, you first need to uninstall it. This can be done by :

sudo apt-get remove icaclient

Once that’s done, we need to head over to the Citrix Download site and get hold of the previous version of the Receiver, in this case 13.4.

First, we need to go to the Citrix Receiver Downloads Page and find the section for “Earlier Versions of Receiver for Linux”

In our case we select the link to take us to the download page for version 13.4.

I selected the Full Package (64-bit version) :

Accept the licence agreement and a short while later, you should have a new file in your Downloads folder ( or wherever you chose to store it) :

ls -l icaclient*

-rw-rw-r-- 1 mike mike 19000146 Jun 19 12:18 icaclient_13.5.0.10185126_amd64.deb

To install…

sudo gdebi icaclient_13.4.0.10109380_amd64.deb 

To verify the installation we can now run dpkg again…

dpkg -l icaclient

…which this time should say …

Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                          Version                     Architecture                Description
+++-=============================================-===========================-===========================-===============================================================================================
ii  icaclient                                     13.4.0.10109380             amd64                       Citrix Receiver for Linux

After all of that, I do now have the option of working from home rather than catching the bus.


Filed under: Linux, Mint, Ubuntu Tagged: apt-get remove, Citrix Receiver for Linux, dpkg, gdebi, icaclient

Installing SQLDeveloper and SQLCL on CentOS

Mon, 2017-06-19 14:02

As is becoming usual in the UK, the nation has been left somewhat confused in the aftermath of yet another “epoch-defining” vote.
In this case, we’ve just had a General Election campaign in which Brexit – Britain’s Exit from the EU – played a vanishingly small part. However, the result is now being interpreted as a judgement on the sort of Brexit that is demanded by the Great British Public.
It doesn’t help that, beyond prefixing the word “Brexit” with an adjective, there’s not much detail on the options that each term represents.
Up until now, we’ve had “Soft Brexit” and “Hard Brexit”, which could describe the future relationship with the EU but equally could be how you prefer your pillows.
Suddenly we’re getting Open Brexit and even Red-White-and-Blue Brexit.
It looks like the latest craze sweeping the nation is Brexit Bingo.
This involves drawing up a list of adjectives and ticking them off as they get used as a prefix for the word “Brexit”.
As an example, we could use the names of the Seven Dwarfs. After all, no-one wants a Dopey Brexit, ideally we’d like a Happy Brexit but realistically, we’re likely to end up with a Grumpy Brexit.

To take my mind off all of this wacky word-play, I’ve been playing around with CentOS again. What I’m going to cover here is how to install Oracle’s database development tools and persuade them to talk to a locally installed Express Edition database.

Specifically, I’ll be looking at :

  • Installing the appropriate Java Developer Kit (JDK)
  • Installing and configuring SQLDeveloper
  • Installing SQLCL

Sound like a Chocolate Brexit with sprinkles ? OK then…

Environment

I’m running on CentOS 7 (64 bit). I’m using the default Gnome 3 desktop (3.1.4.2).
CentOS is part of the Red Hat family of Linux distros which includes Red Hat, Fedora and Oracle Linux. If you’re running on one of these distros, or on something that’s based on one of them then these instructions should work pretty much unaltered.
If, on the other hand, you’re running a Debian based distro ( e.g. Ubuntu, Mint etc) then you’ll probably find these instructions rather more useful.

I’ve also got Oracle Database 11gR2 Express Edition installed locally. Should you feel so inclined, you can perform that install on CentOS using these instructions.

One other point to note, I haven’t bothered with any Oracle database client software on this particular machine.

Both SQLDeveloper and SQLCL require Java so…

Installing the JDK

To start with, we’ll need to download the JDK version that SQLDeveloper needs to run against. At the time of writing ( SQLDeveloper 4.2), this is Java 8.

So, we need to head over to the Java download page
… and download the appropriate rpm package. In our case :

jdk-8u131-linux-x64.rpm

Once the file has been downloaded, open the containing directory in Files, right-click our new rpm and open it with Software Install :

Now press the install button.

Once it’s all finished, you need to make a note of the directory that the jdk has been installed into as we’ll need to point SQLDeveloper at it. In my case, the directory is :

/usr/java/jdk1.8.0_131

Speaking of SQLDeveloper…

SQLDeveloper

Head over to the SQLDeveloper Download Page and get the latest version. We’re looking for the ??? option. In my case :

sqldeveloper-4.2.0.17.089.1709-1.noarch.rpm

While we’re here, we may as well get the latest SQLCL version as well. The download for this is a single file as it’s platform independent.

Once again, we can take advantage of the fact that Oracle provides us with an rpm file by right-clicking it in Files and opening with Software Install.

Press the install button and wait for a bit…

Once the installation is complete, we need to configure SQLDeveloper to point to the JDK we’ve installed. To do this, we need to run :

sh /opt/sqldeveloper/sqldeveloper.sh

…and provide the jdk path when prompted, in this case :

/usr/java/jdk1.8.0_131

The end result should look something like this :

In my case I have no previous install to import preferences from so I’ll hit the No button.

Once SQLDeveloper opens, you’ll want to create a connection to your database.

To do this, go to the File Menu and select New/Connection.

To connect as SYSTEM to my local XE database I created a connection that looks like this :

Once you’ve entered the connection details, you can hit Test to confirm that all is in order and you can actually connect to the database.
Provided all is well, hit Save and the Connection will appear in the Tree in the left-side of the tool from this point forward.

One final point to note, as part of the installation, a menu item for SQLDeveloper is created in the Programming Menu. Once you’ve done the JDK configuration, you can start the tool using this menu option.

SQLCL

As previously noted, SQLCL is a zip file rather than an rpm, so the installation process is slightly different.
As with SQLDeveloper, I want to install SQLCL in /opt .
To do this, I’m going to need to use sudo so I have write privileges to /opt.

To start with then, open a Terminal and then start files as sudo for the directory that holds the zip. So, if the directory is $HOME/Downloads …

sudo nautilus $HOME/Downloads

In Files, right click the zip file and select Open With Archivte Manager

Click the Extract Button and extract to /opt

You should now have a sqlcl directory under /opt.

To start sqlcl, run

/opt/sqlcl/bin/sql

…and you should be rewarded with…

There, hopefully that’s all gone as expected and you’ve not been left with a Sneezy Brexit.


Filed under: Linux, Oracle, SQLDeveloper Tagged: jdk, sqlcl, SQLDeveloper, sudo nautilus

Dude, Where’s My File ? Finding External Table Files in the midst of (another) General Election

Mon, 2017-05-22 16:11

It’s early summer in the UK, which means it must be time for an epoch defining vote of some kind. No, I’m not talking about Britain’s Got Talent.
Having promised that there wouldn’t be another General Election until 2020, our political classes have now decided that they can’t go any longer without asking us what we think. Again.
Try as I might, it may not be possible to prevent the ear-worm phrases from the current campaign slipping into this post.
What I want to look at is how you can persuade Oracle to tell you the location on disk of any files associated with a given external table.
Specifically, I’ll be covering :

  • getting the name of the Database Server
  • finding the fully qualified path of the datafile the external table is pointing to
  • finding other files associated with the table, such as logfiles

In the course of this, we’ll be challenging the orthodoxy of Western Capitalism “If You Can Do It In SQL…” with the principle of DRY ( Don’t Repeat Yourself).
Hopefully I’ll be able to come up with a solution that is “Strong and Stable” and yet at the same time “Works For The Many, Not the Few”…

The Application

For the most part, I’ve written this code against Oracle 11g Express Edition. However, there are two versions of the final script, one of which is specifically for 12c. I’ll let you know which is which when we get there.

I have an external table which I use to load data from a csv file.

Initially, our application’s external table looks like this :

create table plebiscites_xt
(
    vote_year number(4),
    vote_name varchar2(100)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'plebiscites.bad'
            logfile 'plebiscites.log'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )
        )
            location('plebiscites.csv')
    )
reject limit unlimited
/

I’ve created the table in the MIKE schema.

The file that we’re currently loading – plebiscites.csv contains the following :

year,vote_name
2014,Scottish Independence Referendum
2015,UK General Election
2016,EU Referendum
2017,UK General Election

For the purposes of this exercise, I’ll assume that the file is uploaded frequently ( say once per day). I’ll also assume that there’s some ETL process that loads the data from the external table into a more permanent table elsewhere in the database.

As is the nature of this sort of ETL, there are times when it doesn’t quite work as planned.
This is when, equipped with just Read-Only access to production, you will need to diagnose the problem.

In these circumstances, just how do you locate any files that are associated with the external table?
Furthermore, how do you do this without having to create any database objects of your own ?

Finding the server that the files are on

There are a couple of ways to do this.
You could simply look in V$INSTANCE…

select host_name 
from v$instance
/

Alternatively…

select sys_context('userenv', 'server_host')
from dual
/

…will do the same job.
Either way, you should now have the name of the server that your database is running on and, therefore, the server from which the file in question will be visible.
Now to find the location of the data file itself…

Finding the datafile

In keeping with the current standard of public discourse, we’re going to answer the question “How do you find an External Table’s current Location File when not connected as the table owner” by answering a slightly different question ( i.e. as above but as the table owner)…

Our search is simplified somewhat by the fact that the location of any external table is held in the _EXTERNAL_LOCATIONS dictionary views :

select directory_owner, directory_name, location
from user_external_locations
where table_name = 'PLEBISCITES_XT'
/

With this information, we can establish the full path of the file by running…

select dir.directory_path||'/'||uel.location as xt_file
from user_external_locations uel
inner join all_directories dir
    on dir.owner = uel.directory_owner
    and dir.directory_name = uel.directory_name
where uel.table_name = 'PLEBISCITES_XT'
/

…which results in…

XT_FILE                                                                        
--------------------------------------------------------------------------------
/u01/app/oracle/my_files/plebiscites.csv                                        

This is all rather neat and simple. Unfortunately, our scenario of having to investigate an issue with the load is likely to take place in circumstances that render all of this of limited use, at best.

Remember, the scenario here is that we’re investigating an issue with the load on a production system. Therefore, it’s quite likely that we are connected as a user other than the application owner.
In my case, I’m connected as a user with CREATE SESSION and the LOOK_BUT_DONT_TOUCH role, which is created as follows :

create role look_but_dont_touch
/

grant select any dictionary to look_but_dont_touch
/

grant select_catalog_role to look_but_dont_touch
/

As well as the table’s data file, we’re going to want to look at any logfiles, badfiles and discardfiles associated with the table.

Finding other External Table files

At this point it’s worth taking a look at how we can find these additional files. Once again, we have two options.
First of all, we can simply check the table definition using DBMS_METADATA…

set long 5000
set pages 100
select dbms_metadata.get_ddl('TABLE', 'PLEBISCITES_XT', 'MIKE')
from dual
/

…alternatively, we can use the _EXTERNAL_TABLES to home in on the ACCESS_PARAMTERS defined for the table…

set long 5000
select access_parameters
from dba_external_tables
where owner = 'MIKE'
and table_name = 'PLEBISCITES_XT'
/

For our table as it’s currently defined, this query returns :

records delimited by newline
            badfile 'plebiscites.bad'
            logfile 'plebiscites.log'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )

In either case, we end up with a CLOB that we need to search to find the information we need.
To do this programatically, you may be tempted to follow the time-honoured approach of “If you can do it in SQL, do it in SQL”…

with exttab as
(
    select dir.directory_path,  
        regexp_replace( ext.access_parameters, '[[:space:]]') as access_parameters
    from dba_external_tables ext
    inner join dba_directories dir
        on dir.owner = ext.default_directory_owner
        and dir.directory_name = ext.default_directory_name
    where ext.owner = 'MIKE' 
    and ext.table_name = 'PLEBISCITES_XT'
)
select directory_path||'/'||
    case when instr(access_parameters, 'logfile',1,1) > 0 then
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'logfile') +8, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'logfile') +8, 1) - (instr(access_parameters, 'logfile') +8) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as log_file_name,
    directory_path||'/'||
    case when instr(access_parameters, 'badfile',1,1) > 0 then
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'badfile') +8, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'badfile') +8, 1) - (instr(access_parameters, 'badfile') +8) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as bad_file_name,
    directory_path||'/'||
    case when instr(access_parameters, 'discardfile',1,1) > 0 then    
        substr
        ( 
            access_parameters, 
            instr(access_parameters, 'discardfile') +12, -- substring start position
            instr(access_parameters, chr(39), instr(access_parameters, 'discardfile') +12, 1) - (instr(access_parameters, 'discardfile') +12) -- substr number of characters
        ) 
        else to_clob('Filename not specified')    
    end as discard_file_name    
from exttab    
/

…which returns…

Hmmm, it’s possible that a slightly more pragmatic approach is in order here…

set serveroutput on size unlimited
declare
    function get_file
    ( 
        i_owner in dba_external_tables.owner%type,
        i_table in dba_external_tables.table_name%type, 
        i_ftype in varchar2
    )
        return varchar2
    is
        separator constant varchar2(1) := '/';
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        start_pos pls_integer := 0;
        end_pos pls_integer := 0;
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.owner = upper(i_owner) 
        and ext.table_name = upper(i_table);

        start_pos := instr( access_params, i_ftype||chr(39),1,1) + length(i_ftype||chr(39));
        if start_pos - length(i_ftype||chr(39)) = 0 then
            return 'Filename Not Specified';
        end if;    
        end_pos := instr(access_params, chr(39), start_pos, 1);
        return dir_path||separator||substr(access_params, start_pos, end_pos - start_pos);
    end get_file;

begin
    dbms_output.put_line('LOGFILE '||get_file('MIKE', 'PLEBISCITES_XT', 'logfile'));
    dbms_output.put_line('BADFILE '||get_file('MIKE', 'PLEBISCITES_XT','badfile'));
    dbms_output.put_line('DISCARDFILE '||get_file('MIKE', 'PLEBISCITES_XT','discardfile'));
end;
/

Yes, it’s PL/SQL. No, I don’t think I’ll be getting a visit from the Database Police as this is a rather more DRY method of doing pretty much the same thing…

LOGFILE /u01/app/oracle/my_files/plebiscites.log
BADFILE /u01/app/oracle/my_files/plebiscites.bad
DISCARDFILE Filename Not Specified


PL/SQL procedure successfully completed.

As we’re about to find out, this solution also falls short of being a panacea…

Separate Directory Definitions

What happens when the directories that the files are created in are different from each other ?
Let’s re-define our table :

drop table plebiscites_xt
/

create table plebiscites_xt
(
    vote_year number(4),
    vote_name varchar2(100)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'plebiscites.bad'
            logfile my_files_logs:'plebiscites.log'
            discardfile my_files_discards:'plebiscites.disc'
            skip 1
            fields terminated by ','
            (
                vote_year integer external(4),
                vote_name char(100)
            )
        )
            location('plebiscites.csv')
    )
reject limit unlimited
/

You’ll notice here that we’ve added a discard file specification. More pertinently, the directory location for both the discard file and the log file are now specified.
Therefore, our solution needs some tweaking to ensure that it is fit for the many. In fact, while we’re at it, we may as well add the location file in as well….

set serveroutput on size unlimited
declare
    separator constant varchar2(1) := chr(47); -- '/'

    loc_dir_path dba_directories.directory_path%type;
    loc_file user_external_locations.location%type;
    
    function get_file( i_table user_external_tables.table_name%type, i_ftype in varchar2)
        return varchar2
    is
        squote constant varchar2(1) := chr(39); -- " ' "
        colon constant varchar2(1) := chr(58); -- ':'
        
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        
        filedef_start pls_integer := 0;
        filedef_end pls_integer := 0;
        filedef_str clob;
        
        dir_defined boolean;
        
        dir_start pls_integer := 0;
        dir_end pls_integer := 0;
        
        dir_name dba_directories.directory_name%type;
        
        fname_start pls_integer := 0;
        fname_end pls_integer := 0;
        
        fname varchar2(4000);
        
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.table_name = upper(i_table);
        
        filedef_start := instr(access_params, i_ftype, 1,1); 
        
        if filedef_start = 0 then
            return 'Filename Not Specified';
        end if;
        filedef_end := instr(access_params, squote, filedef_start, 2) + 1;
        filedef_str := substr(access_params, filedef_start, filedef_end - filedef_start);

        dir_defined := instr( filedef_str, colon, 1, 1) > 0;
        if dir_defined then 

            dir_start := length(i_ftype) + 1; 
            dir_end := instr( filedef_str, colon, 1, 1);
            dir_name := substr(filedef_str, dir_start, dir_end - dir_start);
            begin
                select directory_path
                into dir_path
                from dba_directories
                where directory_name = upper(dir_name);
            exception when no_data_found then
                return 'The directory object specified for this file does not exist';
            end;    
        end if;    
        
        fname_start := instr(filedef_str, squote, 1, 1) + 1; 
        fname_end := instr(filedef_str, squote, 1, 2);
        fname := substr( filedef_str, fname_start, fname_end - fname_start);
        return dir_path||separator||fname;
    end get_file;

begin
    -- Get the current file that the XT is pointing to 
    select dir.directory_path, ext.location
        into loc_dir_path, loc_file 
    from dba_external_locations ext
    inner join dba_directories dir
        on dir.owner = ext.directory_owner
        and dir.directory_name = ext.directory_name
        and ext.table_name = 'PLEBISCITES_XT';
        
    dbms_output.put_line('LOCATION '||loc_dir_path||separator||loc_file);    
    dbms_output.put_line('LOGFILE '||get_file('PLEBISCITES_XT', 'logfile'));
    dbms_output.put_line('BADFILE '||get_file('PLEBISCITES_XT','badfile'));
    dbms_output.put_line('DISCARDFILE '||get_file('PLEBISCITES_XT','discardfile'));
    dbms_output.put_line('PREPROCESSOR '||get_file('plebiscites_xt', 'preprocessor'));
end;
/

Run this and we get :

LOCATION /u01/app/oracle/my_files/plebiscites.csv
LOGFILE /u01/app/oracle/my_files/logs/plebiscites.log
BADFILE /u01/app/oracle/my_files/plebiscites.bad
DISCARDFILE /u01/app/oracle/my_files/discards/plebiscites.disc
PREPROCESSOR Filename Not Specified


PL/SQL procedure successfully completed.

Having made such a big thing of preferring the DRY principle to the “Do it in SQL” doctrine, I feel it’s only fair to point out that the new features of the WITH clause in 12c does tend to blur the line between SQL and PL/SQL somewhat…

set lines 130
column ftype format a20
column file_path format a60
with function get_file( i_table in dba_external_tables.table_name%type, i_ftype in varchar2)
    return varchar2
    is
    
        separator constant varchar2(1) := chr(47); -- '/'
        squote constant varchar2(1) := chr(39); -- " ' "
        colon constant varchar2(1) := chr(58); -- ':'
        
        dir_path dba_directories.directory_path%type;
        access_params dba_external_tables.access_parameters%type;
        
        filedef_start pls_integer := 0;
        filedef_end pls_integer := 0;
        filedef_str clob;
        
        dir_defined boolean;
        
        dir_start pls_integer := 0;
        dir_end pls_integer := 0;
        
        dir_name dba_directories.directory_name%type;
        
        fname_start pls_integer := 0;
        fname_end pls_integer := 0;
        
        fname varchar2(4000);
        
    begin
        select dir.directory_path, lower(regexp_replace(ext.access_parameters, '[[:space:]]'))
        into dir_path, access_params
        from dba_external_tables ext
        inner join dba_directories dir
            on dir.owner = ext.default_directory_owner
            and dir.directory_name = ext.default_directory_name
        where ext.table_name = upper(i_table);
        
        filedef_start := instr(access_params, i_ftype, 1,1); 
        
        if filedef_start = 0 then
            return 'Filename Not Specified';
        end if;
        filedef_end := instr(access_params, squote, filedef_start, 2) + 1;
        filedef_str := substr(access_params, filedef_start, filedef_end - filedef_start);

        dir_defined := instr( filedef_str, colon, 1, 1) > 0;
        if dir_defined then 

            dir_start := length(i_ftype) + 1; 
            dir_end := instr( filedef_str, colon, 1, 1);
            dir_name := substr(filedef_str, dir_start, dir_end - dir_start);
            begin
                select directory_path
                into dir_path
                from dba_directories
                where directory_name = upper(dir_name);
            exception when no_data_found then
                return 'The directory object specified for this file does not exist';
            end;    
        end if;    
        
        fname_start := instr(filedef_str, squote, 1, 1) + 1; 
        fname_end := instr(filedef_str, squote, 1, 2);
        fname := substr( filedef_str, fname_start, fname_end - fname_start);
        return dir_path||separator||fname;
    end get_file;

select 'LOCATION ' as ftype, dir.directory_path||sys_context('userenv', 'platform_slash')||ext.location as file_path
from user_external_locations ext
inner join dba_directories dir
    on dir.owner = ext.directory_owner
    and dir.directory_name = ext.directory_name
    and ext.table_name = 'PLEBISCITES_XT'
union select 'LOGFILE', get_file('plebiscites_xt', 'logfile') from dual
union select 'BADFILE', get_file('plebiscites_xt', 'badfile') from dual
union select 'DISCARDFILE', get_file('plebiscites_xt', 'discardfile') from dual
union select 'PREPROCESSOR', get_file('plebiscites_xt', 'preprocessor') from dual
/

Hopefully that’s something to think about in between the Party Election Broadcasts.


Filed under: Oracle, PL/SQL, SQL Tagged: 'server_host'), 12c, 12c pl/sql function in with clause, dba_external_locations, dba_external_tables, don't repeat yourself, external table badfile, external table discardfile, external table logfile, external tables, sys_context('userenv'

Having a mid-life crisis on top-of-the-range hardware

Mon, 2017-05-08 17:31

I’ve recently begun to realise that I’m not going to live forever.
“Surely not”, you’re thinking, “look at that young scamp in the profile picture, he’s not old enough to be having a mid-life crisis”.

Well, five minutes ago, that was a recent picture. Suddenly, it’s more than 10 years old. As Terry Pratchett once observed, “Inside every old person is a young person wondering what happened”.

Fortunately, with age comes wisdom…or a sufficiently good credit rating with which to be properly self-indulgent.
Now, from what I’ve observed, men who get to my stage in life seem to seek some rather fast machinery as a cure for the onset of morbid reflections on the nature of their own mortality.
In this case however, it’s not the lure of a fast care that I’ve succumbed to. First and foremost, I am a geek. And right now, I’m a geek with a budget.

Time then to draw up the wish list for my new notebook. It will need to…

  • be bigger than my 10-inch netbook but small enough to still be reasonably portable
  • have a fast, cutting-edge processor
  • have an SSD with sufficient storage for all my needs
  • have large quantities of RAM
  • come with a Linux Operating System pre-installed

For any non-technical readers who’ve wandered down this far, the rough translation is that I want something with more silicon in it than one of those hour-glasses for measuring the time left before Brexit that have been on the telly recently.
It’s going to have to be so fast that it will, at the very least, offer Scotty the prospect of changing the Laws of Physics.
Oh, and I should still be able to use it on the train.

The requirement for a pre-installed Linux OS may be a factor which limits my choices.
Usually, I’m happy enough to purchase a machine with Windows pre-installed and then replace it with a Linux Distro of my choice.
Yes, this may involve some messing about with drivers and – in some cases – a kernel upgrade, but the process is generally fairly painless.
This time though, I’m going to be demanding. However much of a design classic a Mac may be, OSX just isn’t going to cut it. Linux is my OS of choice.
Furthermore, if I’m going to be paying top dollar for top-of-the range then I want everything to work out of the box.
Why? (pause to flick non-existent hair) Because I’m worth it.

Oh, as a beneficial side-effect it does also mean that I’ll save myself a few quid because I won’t have to fork out for a Windows License.

In the end, a combination of my exacting requirements and the advice and guidance of my son, who knows far more about this sort of thing, lead me to my final choice – the Dell XPS13

What follows is in the style of an Apple fanboy/fangirl handling their latest iThing…

Upon delivery, the package was carried to the kitchen table where it lay with all it’s promise of untold joy…

Yea, and there followed careful unwrapping…

It’s a….box

…Russian doll-like…

…before finally…

If Geekgasm isn’t a thing, it jolly well should be.

Now to setup the OS…

…before finally re-starting.

The first re-boot of a machine usually takes a little while as it sorts itself out so I’ll go and make a cof… oh, it’s back.
Yep, Ubuntu plus SSD ( 512GB capacity) plus a quad-core i7-7560 CPU equals “are you sure you actually pressed the button ?”

Ubuntu itself wasn’t necessarily my Linux distro of choice. That doesn’t matter too much however.
First of all, I’m quite happy to get familiar with Unity if it means I can still access all of that Linux loveliness.
Secondly, with the insane amount of system resources available( 16GB RAM to go with that CPU), I can simply spin up virtual environments with different linux distros, all sufficiently fast to act as they would if being run natively.
For example…

Right, now I’ve got that out of my system, I can wipe the drool off the keyboard and start to do something constructive…like search for cat videos.


Filed under: Uncategorized Tagged: xps13

Kicking the habit of WM_CONCAT for a delimited list of rows, with LISTAGG

Tue, 2017-05-02 15:40

I gave up smoking recently.
Among other bad habits that I need to kick is using the (not so) trusty WM_CONCAT.

Say I want to get a record set consisting a comma-delimited list of columns in the EMPLOYEES table. In the past, this may have been somewhat challenging to do in a single SQL query, unless you knew about the undocumented WM_CONCAT…

select wm_concat(column_name)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES';

From around 10g, right up to 11g R2 Enterprise Edition, this function would return your result set in a single, comma-delimited list.
However, if you attempt to execute the same query in 12g, or even 11g Express Edition, you’ll get a nasty surprise …

Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "WM_CONCAT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Fortunately, a more modern (and supported) alternative has been around since 11g…

select listagg( column_name, ',') within group( order by column_id)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES'
/

LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBYCOLUMN_ID)
----------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID

Unlike WS_CONCAT, LISTAGG allows you to specify the order in which the delimited values should be concatenated. It also allows you to specify the delimiter to use.
So you could use a “|” symbol, for example, or, if you have definite ideas about how a list of columns should be written you may consider something like :

select listagg( column_name, chr(10)||',') within group( order by column_id)
from user_tab_cols
where hidden_column = 'NO'
and table_name = 'EMPLOYEES'
/

LISTAGG(COLUMN_NAME,CHR(10)||',')WITHINGROUP(ORDERBYCOLUMN_ID)
--------------------------------------------------------------------------------
EMPLOYEE_ID                                                                     
,FIRST_NAME                                                                     
,LAST_NAME                                                                      
,EMAIL                                                                         
,PHONE_NUMBER                                                                   
,HIRE_DATE                                                                      
,JOB_ID                                                                         
,SALARY                                                                         
,COMMISSION_PCT                                                                 
,MANAGER_ID                                                                     
,DEPARTMENT_ID                      

Now, if only I could remember not to squeeze the toothpaste tube in the middle…


Filed under: Oracle, SQL Tagged: listagg, wm_concat

The Rest of the Django App – the View and Controller Tiers

Fri, 2017-04-21 15:27

As is the way of Software Projects, I’m starting to get a bit of pressure from the customer about delivery.
As is slightly less usual in such circumstances, the question I’m being asked is “when are you going to get out there and mow that lawn ?”
Fortunately, Django is “for perfectionists with deadlines” …or minions with gardening chores waiting (probably) so I’d better crack on.

Now, I could do with some assistance. Fortunately, these guys will be around to help :

Pay bananas, get minions.

In case you haven’t been following the story to date, this project is to create an Application to allow my better half to look at which movies we have on DVD or Blu-Ray.

So far my Django journey has consisted of :

Django follows the Model-View-Controller (MVC) pattern of application design. Having spent some time looking at the Database (Model) layer, we’re now going to turn our attention to the View (what the end-user sees) and the Controller ( the application logic that makes the application work).

Recap of the Current Application state

After developing the data model, which looks like this :

…the application codebase currently looks like this :

tree -L 2 --dirsfirst --noreport

We have a useable database for our application. Now we need to provide a means of presenting the application data to our users.

Before I go any further, I’m going to try and simplify matters somewhat when it comes to talking about file locations.

I’m going to set an environment variable called $DJANGO_HOME to hold the root directory of the Python virtual environment we’ll be using.
This is the directory that has manage.py in it.

To do this, I’ve written the following shell script, which also starts the virtual environment :

#!/bin/sh

export DJANGO_HOME=`pwd`
source dvdsenv/bin/activate
echo $DJANGO_HOME

Once we’ve granted execute permissions on the script…

chmod a+x set_virt.sh

…we can set our environment variable and start the virtual environment…

. ./set_virt.sh

For the remainder of this post, I’ll be referencing file locations using $DJANGO_HOME to denote the root directory of the python virtual environment.

Now, let’s take a first look at how to retrieve the application data from the database and present it to the users…

Our First Django Page

In $DJANGO_HOME/dvds we need to create some controller code. Confusingly, this is in a file called views.py :

from django.shortcuts import render
from django.views.generic import ListView

from .models import Title

class TitleList(ListView) :
    model = Title

There’s not really much to it. A simple ListView class based on the Title model object, which will list all of the records in the Title table.

Now, $DJANGO_HOME/dvds/urls.py (having stripped out the default comments for brevity):

from django.conf.urls import url
from django.contrib import admin

from dvds.views import TitleList
app_name = 'dvds'

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^$', TitleList.as_view(), name="main"),
]

So, we’ve imported the TitleList view we’ve just created in views.py and then added a pattern so that we can navigate to it.

If we go ahead and run this now, we probably won’t get the result we’re hoping for …

…so let’s make one…

First, we want to create a directory in which to hold the templates. We’ll also want to keep templates from different applications separate so…

cd $DJANGO_HOME/dvds
mkdir templates
cd templates
mkdir dvds

Now, in the newly created directory, we want to create a file called title_list.html, which looks something like this…

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Deb's DVD Library</title>
    </head>
    <body>
        <h1>DVD Library List</h1>
        <table border="1">
            <tr>
                <th>Title</th>
                <th>Released</th>
                <th>Certificate</th>
                <th>Format</th>
                <th>Director</th>
                <th>Synopsis</th>
                <th>series</th>
                <th>No. In Series</th>
                <th>Categories</th>
            </tr>
            {% for title in object_list %}
                <tr>
                    <td>{{title.title_name}}</td>
                    <td>{{title.year_released}}</td>
                    <td>{{title.bbfc_certificate}}</td>
                    <td>{{title.get_media_format_display}}</td>
                    <td>{{title.director}}</td>
                    <td>{{title.synopsis}}</td>
                    <td>{{title.series}}</td>
                    <td>{{title.number_in_series}}</td>
                    <td>
                        {% for cat in title.categories.all %}
                            {{cat}}&nbsp;
                        {% endfor %}
                    </td>
                </tr>
            {% endfor %}
        </table>
    </body>
</html>

This should look fairly familiar if you’ve used languages such as PHP. Effectively, the {% %} tags indicate programmatic structures ( in this case, for loops), and the {{}} encase actual values pulled from the database.

Examples of this in the above listing include :

  • Line 20 – loop through the Object List
  • Line 25 – display the plain db column values until here, where we use the built-in “get display” to retrieve the display value from the MEDIA_FORMAT_CHOICE list we’ve assigned to the media_format column.
  • Line 31 – loop through all the values in the nested category column.
  • Line 36 – close the for loop

The effect of this is quite exciting… from a programming perspective ….

Not only have we managed to display all of the Title data we’ve added to our application, we can also see that :

  • Django is smart enough to return the name of the Series rather than the series id
  • we can display all of the categories by means of a for loop through the categories column in the Titles table

At this point thought, the look-and-feel is rather…utilitarian. Not only that, it looks like I may be in for some fairly tedious copy-and-paste action unless I can find a way to re-use some of this code.
Maybe Django can help…

A Site Map

Before we go too much further, it’s probably worth pausing to consider which pages our application will ultimately comprise of.
We already have our main page, but we’ll probably want to search for titles in our database.
Given that all the DML in the application is done through the Django Admin site that we get out of the box, we’ll also need to link to that.

Ultimately then, our application pages will be :

  • Main Page – listing of titles in the library
  • Search Page – to enter search criteria
  • Results Page – to display search results

To make the application look a bit nicer, each page will need to use the same style sheet.

cd $DJANGO_HOME/dvds
mkdir static
mkdir static/css
mkdir static/images

The file – $DJANGO_HOME/dvds/static/css/style.css looks like this…

*
{
    font-family: sans-serif;
    font-size: large;
    background: #e4ffc9;
}

table 
{
    border-collapse: collapse;
}
table, th, td
{
    border: 1px solid black;
    text-align: left;
    vertical-align: top;
}

.explanation
{
    display: table-row;
}


.explanation .landscape
{
    vertical-align: middle;
    height: 150px;
    width: 300px;
}


.explanation .portrait
{
    vertical-align: middle;
    height: 250px;
    width: 200px;    
}

.explanation textarea
{
    vertical-align: top;
    height: 150px;
    width: 400px;
    border: none;
    background: none;
    font-size: large;
}

.row
{
    display: table-row;
}

#search_form label
{
    display : table-cell;
    text-align : right;
}

#search_form input, select
{
    display : table-cell;
    width : 300px;
}

#search_form input[type=submit]
{
    font-size : large;
    font-weight : bold;
    border-radius:25px;
    background-color : #2FC7C9;
}

Once we’ve added the style sheet, along with the images we’re going to use in the application, the file layout in the static directory should look like this :

From a layout perspective, I’d like each page to have :

  • a navigation bar
  • in-line explainatory text about what to do on the page
  • the page specific content

If only I could apply a template for this…

Templates

First off we can create a template, called base.html which will serve to apply the layout to all of our application pages.
Essentially, the pages can inherit this template and then override the various sections (blocks).
We can also use this template to include all of the tedious HTML header stuff, as well as a link to our application style sheet.

The file is saved in the same location as our existing template ($DJANGO_HOME/dvds/templates/dvds) and looks like this :

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf8">
        <title>{% block title %}Base Title{% endblock %}</title>
        <link rel="stylesheet" type="text/css" href="/static/css/style.css" />
    </head>
    <body>
        {% block breadcrumb %}{% endblock %}
        {% block explanation %}{% endblock %}
        {% block page_content %}{% endblock %}
    </body>
</html> 

Before we incorporate this into our main page, we also need to consider that the code to list titles can be used in the application results page as well as in the main page. Fortunately, we can also move this out into a template.
The file is called display_titles.html and is saved in the same templates directory as all the other html files…

<table>
    <tr>
        <th>Title</th> <th>Released</th> <th>Certificate</th>
        <th>Format</th> <th>Director</th>
        <th>Synopsis</th> <th>Series</th> <th>No. In Series</th>
        <th>Categories</th> 
    </tr>
    {% for title in titles %}
        <tr>
            <td>{{title.title_name}}</td> <td>{{title.year_released}}</td> <td>{{title.bbfc_certificate}}</td>
            <td>{{title.get_media_format_display}}</td>
            <td>{{title.director}}</td> <td>{{title.synopsis}}</td> <td>{{title.series}}</td>
            <td>{{title.number_in_series}}</td>
            <td>
                {% for cat in title.categories.all %}
                    {{cat}}&nbsp;
                {% endfor %}
            </td>
        </tr>
    {% endfor %}
</table>

The final template component of our application is the navigation menu. The template is called breadcrumb.html and includes all of the breadcrumb menu entries in the application :

<img src="/static/images/deb_movie_logo.jpg">&nbsp;
{% if home %}<a href="/">Home</a>&nbsp;{% endif %}
{% if admin %}|&nbsp;<a href="admin" target="_blank">Add or Edit Titles</a>{% endif %}
{% if back %}|&nbsp;<a href="javascript:history.back(-1)">Back</a>&nbsp;{% endif %}
{% if search_refine %}|&nbsp;<a href="javascript:history.back(-1)">Refine Search</a>&nbsp;{% endif %}
<!-- additional logic required to see if search is first option -->
{% if search %}|&nbsp;<a href="/search">Search</a>{% endif %}
{% if search_again %}|&nbsp;<a href="/search">Search Again</a>{% endif %}

What navigation items are displayed are dependent on how the breadcrumb template is called. Essentially we treat it like a function.

So, putting all of this together in our main page, we get something like :

{% extends "dvds/base.html" %}
{% block title %}Deb's Movie Library{% endblock %}
{% block breadcrumb %}{% include "dvds/breadcrumb.html" with search=True admin=True %}{% endblock %}
{% block explanation %}
    <div class="explanation">
        <h1>So, you'd like to watch a Film...</h1>
        <img src="static/images/avengers.jpg">&nbsp;
        <textarea>We are here to help. 
There's a list of films in the library right here. 
Alternatively, click on "Search" at the top of the page if you're looking for something specific.
        </textarea>
    </div>
{% endblock %}
{% block page_content %}{% include "dvds/display_titles.html" with titles=object_list %}{% endblock %}

To begin with, the extends tag inherits a template as the page parent. The blocks in the remainder of the page are used to override the blocks in the parent template (base.html).

The breadcrumb and page_content blocks use the include tag to import templates into the page.
In the case of the breadcrumb template, we specify the value of the search and admin parameters.
This results in these links being displayed.

When we run the application, the page will look like this :

Before we get there though, it’s probably an idea to put together the first draft of our other application pages…

Search Pages

To start with, we’ll have a fairly simple search screen, which simply involves searching on some user-entered text.

We’ll come onto the pages themselves shortly. First though…

for the search form itself, we’ll need a simple function in $DJANGO_HOME/dvds/views.py :

def search_titles(request):
    return render( request, 'dvds/search.html')

We need to add the appropriate code to execute the search. In views.py, we also add a function called search_results :

def search_results(request) :
    if 'search_string' in request.GET and request.GET['search_string'] :
        titles = Title.objects.filter(title_name__icontains=request.GET['search_string'])
    else :
        titles = Title.objects.all()
    return render( request, 'dvds/results.html', {'titles' : titles, 'search_string' : request.GET['search_string']})

So, if the user submits a search with the search_string populated, then find any titles which contain that string.
The icontains method performs a case insensitive “LIKE” comparison.
If no search string is entered, we’ll display all records in the results.
The search results are then displayed in the dvds/results.html page.
The render function is also passing a couple of arguments to the results page :

  • the titles object containing the search results
  • the original search string entered by the user

In urls.py, we need to add the appropriate entries for these pages :

...
urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^$', TitleList.as_view(), name="main"),
    url(r'^search/$', views.search_titles),
    url(r'^results/$', views.search_results),
]

Once again, the html code resides in the templates directory. It looks rather familiar.
The search form – search.html :

{% extends "dvds/base.html" %}
{% block title %}Find a Movie{% endblock %}

{% block breadcrumb %}{% include "dvds/breadcrumb.html" with home=True %}{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search For A Title</h1>
        <img src="/static/images/spiderman_minion.jpg" >&nbsp;
        <textarea>Enter some text that the title your searching for contains.</textarea>
    </div>
{% endblock %}

{% block page_content %}
    <form id="search_form" action="/results" method="GET">
        <!-- Name of the movie (or part therof) -->
            <label>Title Contains &nbsp;:&nbsp;</label>
            <input name="search_string" placeholder="Words in the Title" />
            <label>&nbsp;</label><input type="submit" value="Find My Movie"/>
    </form>
{% endblock %}

The results page, results.html :

{% extends "dvds/base.html" %}
{% block title %}Search Results{% endblock %}

{% block breadcrumb %}
    {% include "dvds/breadcrumb.html" with home=True back=True search_refine=True search_again=True %}
{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search Results</h1>
        <img src="/static/images/minion_family.jpg" >&nbsp;
       <h2>You searched for titles ...</h2>
       <ul><li>containing the phrase&nbsp;<strong><em>{{search_string}}</em></strong></li></ul>
    </div>
{% endblock %}
{% block page_content %}
    {% if titles %}
        <h2>We found {{titles | length}} title{{ titles|pluralize}}...</h2>
        {% include "dvds/display_titles.html" %}
    {% else %}
        <h2>No Titles matched your search criteria</h2>
    {% endif %}
{% endblock %}

Notice that the call to breadcrumb.html provides different parameter values in the two files.
Now let’s give it all a try…

Click on the Search link from the Home Page and we should get :

Click the “Find My Movie” button and we get :

Refining the Search Functionality

As well as words in the title, it would be useful to be able to search on other criteria such as the Series to which a movie belongs, or maybe the Media Format.

In both cases our application has a limited number of values to select from. The Series table contains a list of all of the series in the application. The MEDIA_FORMAT_CHOICES list contains all of the valid media formats.

By including these objects in $DJANGO_HOME/dvds/views.py

from .models import Title, Series, MEDIA_FORMAT_CHOICES

…we can reference them in the search function we need to create (also in views.py)…

def search_titles(request):
    series_list = Series.objects.all()

    return render( request, 'dvds/search.html', {'series_list' : series_list, 'format_list':MEDIA_FORMAT_CHOICES})

The series_list and format_list objects passed in the call to render can now be used in the html template for the search screen – $DJANGO_HOME/dvds/templates/dvds/search.html :

{% extends "dvds/base.html" %}
{% block title %}Find a Movie{% endblock %}

{% block breadcrumb %}{% include "dvds/breadcrumb.html" with home=True %}{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search For A Title</h1>
        <img class="portrait" src="/static/images/spiderman_minion.jpg" >&nbsp;
        <textarea>Enter some text that the title your searching for contains, and/or a Series and/or a format.
Note - search criteria is addative
        </textarea>
    </div>
    <br />
{% endblock %}

{% block page_content %}
    <form id="search_form" action="/results" method="GET">
        <!-- Name of the movie (or part therof) -->
        <div class="row">    
            <label>Title Contains &nbsp;:&nbsp;</label>
            <input name="search_string" placeholder="Words in the Title" />
        </div>
        <!-- Series drop-down -->
        <div class="row">
            <label>Series&nbsp;:&nbsp;</label>
            <select class="drop_down_list" name="series_id">
                <option value=>None</option>
                {% for series in series_list %}
                    <option value={{series.id}}>{{series.series_name}}</option>
                {% endfor %}
            </select>
        </div>
        <!-- Media Format -->
        <div class="row">
            <label class="search_label">Media Format : </label>
            <select class="drop_down_list" name="media_format">
                <option value=>None</option>
                {% for id, value in format_list %}
                    <option value={{id}}>{{value}}</option>
                {% endfor %}
            </select>
        </div>
        <div class="row">
            <label>&nbsp;</label><input type="submit" value="Find My Movie" />
        </div>
    </form>
{% endblock %}

After all of that, our search screen now looks like this :

As for the search results, well, here’s where things get a bit tricky.
The search function in $DJANGO_HOME/dvds/views.py has changed a bit :

def search_results(request) :
    if 'search_string' in request.GET and request.GET['search_string'] :
        titles = Title.objects.filter(title_name__icontains=request.GET['search_string'])
    else :
        titles = Title.objects.all()
        search_string = None
    if 'series_id' in request.GET and request.GET['series_id'] :
        series_name = Series.objects.filter(pk = request.GET['series_id']).values_list('series_name', flat=True)[0]
        titles = titles.filter(series_id =  request.GET['series_id'])
    else :
        series_name = None
        titles = titles
    if 'media_format' in request.GET and request.GET['media_format'] :
        titles = titles.filter(media_format = request.GET['media_format'])
        # Get the display value to pass to the results page
        media_format = mf_display(request.GET['media_format'])
    else :
        titles = titles
    return render( request, 'dvds/results.html', {'titles' : titles, 'search_string' : request.GET['search_string'], 'series_name' : series_name, 'media_format': media_format})

The first point to note is that, despite the multiple assignment to the titles object, the only database call that will actually be made is the one when render is called. Prior to that, the search conditions will be added. The effect is pretty much the same as building a SQL statement dynamically, adding predicates based on various conditions, before finally executing the finished statement.

We still want to display the search criteria values that were entered.
Whilst looking up display values in the context of a record retrieved from the database is simple enough, the same is not true outside of this context.

For the Series Name, I’ve taken the rather profligate approach of performing an additional database lookup (line 7 in the listing above). In a higher-volume application, you might well look for something a bit less resource intensive.

As for the media format, views.py now also includes this function :

def mf_display( media_format) :
    for (val, mf_name) in MEDIA_FORMAT_CHOICES :
        if val == media_format :
            return mf_name
    return None

…which is invoked on line 16.

This enables us to pass the appropriate values to the results page, $DJANGO_HOME/dvds/templates/dvds/results.html, which now looks like this :

{% extends "dvds/base.html" %}
{% block title %}Search Results{% endblock %}

{% block breadcrumb %}
    {% include "dvds/breadcrumb.html" with home=True search_refine=True search_again=True %}
{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search Results</h1>
        <img class="landscape" src="/static/images/minion_family.jpg" >&nbsp;
       <h2>You searched for titles ...</h2>
       <ul>
            {% if search_string %}
                <li>containing the phrase&nbsp;<strong><em>{{search_string}}</em></strong></li>
            {% endif %}
            {% if series_name %}
                <li>in the series&nbsp;<strong><em>{{series_name}}</em></strong></li>
            {% endif %}
            {% if media_format %}
                <li>on&nbsp;<strong><em>{{media_format}}</em></strong></li>
            {% endif %}
       </ul>
    </div>
{% endblock %}
{% block page_content %}
    {% if titles %}
        <h2>We found {{titles | length}} title{{ titles|pluralize}}...</h2>
        {% include "dvds/display_titles.html" %}
    {% else %}
        <h2>No Titles matched your search criteria</h2>
    {% endif %}
{% endblock %}
The End Result

It’s probably helpful at this point to provide complete listings of all of the code we’ve changed.
This is partly to show that we’ve accomplished a fair bit with Django with surprisingly little code. The more practical reason is to help in the event that I’ve been a bit unclear as to which file a certain code snippet might be in.

Starting in $DJANGO_HOME/dvds we already had the Model layer of our application when we started :

models.py
from django.db import models
from django.core.validators import MinValueValidator

# Allowable values Lists
MEDIA_FORMAT_CHOICES = (
    ('BR', 'Blu Ray'),
    ('DVD', 'DVD'),
)

# British Board of Film Classification Certificates
# as per the official BBFC site - http://www.bbfc.co.uk
BBFC_CHOICES = (
    ('U', 'U'),
    ('PG', 'PG'),
    ('12A', '12A'),
    ('15', '15'),
    ('18', '18'),
    ('R18', 'R18'),
)

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)
        
    def __str__(self):
        return self.category_name

    class Meta :
        # set the default behaviour to be returning categories in alphabetical order by category_name
        ordering = ["category_name"]
        # Define the correct plural of "Category". Among other places, this is referenced in the Admin application
        verbose_name_plural = "categories"

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

    class Meta :
        ordering = ["series_name"]
        verbose_name_plural = "series"

class Title( models.Model) :

    # For optional fields, blank = True means you can leave the field blank when entering the record details
    # null = True means that the column is nullable in the database 
    title_name = models.CharField( max_length = 250)
    year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.
    bbfc_certificate = models.CharField("BBFC Certificate", max_length = 3, choices = BBFC_CHOICES)
    media_format = models.CharField("Format", max_length = 3, choices = MEDIA_FORMAT_CHOICES)
    director = models.CharField(max_length = 100, null=True, blank=True)
    synopsis = models.CharField( max_length = 4000, null = True, blank = True)
    series = models.ForeignKey( Series, on_delete = models.CASCADE, null = True, blank = True)
    number_in_series = models.IntegerField(null = True, blank = True)
    categories = models.ManyToManyField(Category, blank = True)

    class Meta :
        ordering = ["series", "number_in_series", "title_name"]
        # Natural Key for a Title record is title_name, year_released and media_format - we have some films on DVD AND Blu-Ray.
        unique_together = ('title_name', 'year_released', 'media_format',)

    def __str__(self) :
        return self.title_name

Additionally, we now have the following files which pretty much comprise the Controller layer of our MVC application :

urls.py
from django.conf.urls import url
from django.contrib import admin

from dvds.views import TitleList
from dvds import views
app_name = 'dvds'

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^$', TitleList.as_view(), name="main"),
    url(r'^search/$', views.search_titles),
    url(r'^results/$', views.search_results),
]

views.py
from django.shortcuts import render
from django.views.generic import ListView

from .models import Title, Series, MEDIA_FORMAT_CHOICES

def mf_display( media_format) :
    # Get the display value for a MEDIA_FORMAT_CHOICES entry.
    # NOTE this is for use in the search results screen where we confirm
    # the search criteria entered so it's NOT in the context of a Title record at that point.
    for (val, mf_name) in MEDIA_FORMAT_CHOICES :
        if val == media_format :
            return mf_name
    return None

class TitleList(ListView):
    model = Title

def search_titles(request):
    series_list = Series.objects.all()

    return render( request, 'dvds/search.html', {'series_list' : series_list, 'format_list':MEDIA_FORMAT_CHOICES})

def search_results(request) :
    if 'search_string' in request.GET and request.GET['search_string'] :
        titles = Title.objects.filter(title_name__icontains=request.GET['search_string'])
    else :
        titles = Title.objects.all()
        search_string = None
    if 'series_id' in request.GET and request.GET['series_id'] :
        series_name = Series.objects.filter(pk = request.GET['series_id']).values_list('series_name', flat=True)[0]
        titles = titles.filter(series_id =  request.GET['series_id'])
    else :
        series_name = None
        titles = titles
    if 'media_format' in request.GET and request.GET['media_format'] :
        titles = titles.filter(media_format = request.GET['media_format'])
        # Get the display value to pass to the results page
        media_format = mf_display(request.GET['media_format'])
    else :
        titles = titles
        media_format = None
    return render( request, 'dvds/results.html', {'titles' : titles, 'search_string' : request.GET['search_string'], 'series_name' : series_name, 'media_format': media_format})

The View MVC Layer is found in $DJANGO_HOME/dvds/templates/dvds. First, the templates that are extended or included :

base.html
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf8">
        <title>{% block title %}Base Title{% endblock %}</title>
        <link rel="stylesheet" type="text/css" href="/static/css/style.css" />
    </head>
    <body>
        {% block breadcrumb %}{% endblock %}
        {% block explanation %}{% endblock %}
        {% block page_content %}{% endblock %}
    </body>
</html> 
breadcrumb.html
<img src="/static/images/deb_movie_logo.jpg">&nbsp;
{% if home %}<a href="/">Home</a>&nbsp;{% endif %}
{% if admin %}|&nbsp;<a href="admin" target="_blank">Add or Edit Titles</a>{% endif %}
{% if back %}|&nbsp;<a href="javascript:history.back(-1)">Back</a>&nbsp;{% endif %}
{% if search_refine %}|&nbsp;<a href="javascript:history.back(-1)">Refine Search</a>&nbsp;{% endif %}
<!-- additional logic required to see if search is first option -->
{% if search %}|&nbsp;<a href="/search">Search</a>{% endif %}
{% if search_again %}|&nbsp;<a href="/search">Search Again</a>{% endif %}
display_titles.html
<table>
    <tr>
        <th>Title</th> <th>Released</th> <th>Certificate</th>
        <th>Format</th> <th>Director</th>
        <th>Synopsis</th> <th>Series</th> <th>No. In Series</th>
        <th>Categories</th> 
    </tr>
    {% for title in titles %}
        <tr>
            <td>{{title.title_name}}</td> <td>{{title.year_released}}</td> <td>{{title.bbfc_certificate}}</td>
            <td>{{title.get_media_format_display}}</td>
            <td>{{title.director}}</td> <td>{{title.synopsis}}</td> <td>{{title.series}}</td>
            <td>{{title.number_in_series}}</td>
            <td>
                {% for cat in title.categories.all %}
                    {{cat}}&nbsp;
                {% endfor %}
            </td>
        </tr>
    {% endfor %}
</table>

Finally, the application pages themselves, starting with :

title_list.html
{% extends "dvds/base.html" %}
{% block title %}Deb's Movie Library{% endblock %}
{% block breadcrumb %}{% include "dvds/breadcrumb.html" with search=True admin=True %}{% endblock %}
{% block explanation %}
    <div class="explanation">
        <h1>So, you'd like to watch a Film...</h1>
        <img class="landscape" src="static/images/avengers.jpg">&nbsp;
        <textarea>We are here to help. 
There's a list of films in the library right here. 
Alternatively, click on "Search" at the top of the page if you're looking for something specific.
        </textarea>
    </div>
{% endblock %}
{% block page_content %}{% include "dvds/display_titles.html" with titles=object_list %}{% endblock %}

…which looks like this :

search.html
{% extends "dvds/base.html" %}
{% block title %}Find a Movie{% endblock %}

{% block breadcrumb %}{% include "dvds/breadcrumb.html" with home=True %}{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search For A Title</h1>
        <img class="portrait" src="/static/images/spiderman_minion.jpg" >&nbsp;
        <textarea>Enter some text that the title your searching for contains, and/or a Series and/or a format.
Note - search criteria is addative
        </textarea>
    </div>
    <br />
{% endblock %}

{% block page_content %}
    <form id="search_form" action="/results" method="GET">
        <!-- Name of the movie (or part therof) -->
        <div class="row">    
            <label>Title Contains &nbsp;:&nbsp;</label>
            <input name="search_string" placeholder="Words in the Title" />
        </div>
        <!-- Series drop-down -->
        <div class="row">
            <label>Series&nbsp;:&nbsp;</label>
            <select class="drop_down_list" name="series_id">
                <option value=>None</option>
                {% for series in series_list %}
                    <option value={{series.id}}>{{series.series_name}}</option>
                {% endfor %}
            </select>
        </div>
        <!-- Media Format -->
        <div class="row">
            <label class="search_label">Media Format : </label>
            <select class="drop_down_list" name="media_format">
                <option value=>None</option>
                {% for id, value in format_list %}
                    <option value={{id}}>{{value}}</option>
                {% endfor %}
            </select>
        </div>
        <div class="row">
            <label>&nbsp;</label><input type="submit" value="Find My Movie" />
        </div>
    </form>
{% endblock %}

…which, remember, looks like this :

Finally, we have :

results.html

{% extends "dvds/base.html" %}
{% block title %}Search Results{% endblock %}

{% block breadcrumb %}
    {% include "dvds/breadcrumb.html" with home=True search_refine=True search_again=True %}
{% endblock %}

{% block explanation %}
    <div class="explanation">
        <h1>Search Results</h1>
        <img class="landscape" src="/static/images/minion_family.jpg" >&nbsp;
       <h2>You searched for titles ...</h2>
       <ul>
            {% if search_string %}
                <li>containing the phrase&nbsp;<strong><em>{{search_string}}</em></strong></li>
            {% endif %}
            {% if series_name %}
                <li>in the series&nbsp;<strong><em>{{series_name}}</em></strong></li>
            {% endif %}
            {% if media_format %}
                <li>on&nbsp;<strong><em>{{media_format}}</em></s
                trong></li>
            {% endif %}
       </ul>
    </div>
{% endblock %}
{% block page_content %}
    {% if titles %}
        <h2>We found {{titles | length}} title{{ titles|pluralize}}...</h2>
        {% include "dvds/display_titles.html" %}
    {% else %}
        <h2>No Titles matched your search criteria</h2>
    {% endif %}
{% endblock %}

which now looks like this :

Obviously, as more films get added to the library,further enhancements will be needed.
For now though, 1.0 is ready to see the light of day…

Bananas all round !


Filed under: python Tagged: display a choices list name from a value, Django, drop down lists from choices list, drop-down lists from table values, extends by template, get display, include template, ListView, templates, tree command, urls.py, views.py

The Django Fandango Farrago – Looking at Django’s Physical Data Model Design

Wed, 2017-03-15 08:40

I’m sure I’m not the only Oracle Developer who, over the years, has conjured a similar mental image during a planning meeting for a new web-based application…

wibble

…and we’re going to use an ORM

If you want the full gory details as to why this is so troubling from an Oracle database perspective, it is a topic I have covered at length previously.

This time, however, things are different.
Yes, I am somewhat limited in my choice of database due to the hardware my application will run on (Raspberry Pi).
Yes, Django is a logical choice for a framework as I’m developing in Python.
But, here’s the thing, I plan to do a bit of an audit of the database code that Django spits out.
< obligatory-Monty-Python-reference >That’s right Django, No-one expects the Spanish Inquisition ! < obligatory-Monty-Python-reference / >

torturer

Donde esta el Base de datos ?!

I know, this is a character from Blackadder and not Monty Python, but I’ve often regretted the fact that there never seems to be a vat of warm marmalade around (or some kind of gardening implement for that matter), when you enter those all important application architecture discussions at the start of a project.

As a result, one or two further Blackadder references may have crept in to the remainder of this post…

What we’re looking at

The Application I’m developing is as described in my previous post and we’ll be using SQLite as the database for our application.

What I’ll be covering here is :

  • The physical data model we want to implement for our DVD Library Application
  • Using Django to generate the data Model
  • Installation and use of the SQLite command line
  • Tweaking our code to improve the model

We’re not too concerned about performance at this point. The application is low-volume in terms of both data and traffic.
I’ll point out aspects of the code that have a potential performance impact as and when they come up (and I notice them), but performance optimisation is not really the objective here.
The main aim is to ensure that we maximise the benefits of using a relational database by ensuring data integrity.

The target model

By default, Django applies a synthetic key to each table it creates. I have indulged this proclivity in the model that follows, although it’s something I will return to later on.

The application I’m building is a simple catalogue of DVDs and Blu-Rays we have lying around the house.
The main table in this application will be TITLE, which will hold details of each Title we have on Disk.
Note that the Unique Key for this table is the combination of TITLE_NAME, YEAR_RELEASED and MEDIA_FORMAT. Yes I do have some films on both DVD and Blu-Ray.
As for the relationships :

  • a film/tv SERIES may have one, or more than one TITLE
  • a TITLE may belong to one or more CATEGORY
  • a CATEGORY may apply to one or more TITLE

So, in addition to our main data table, TITLE, we need two reference tables – SERIES and CATEGORY. We also need a join table between CATEGORY and TITLE to resolve the many-to-many relationship between them.
Each of the tables will have a Synthetic Key, which makes storing of Foreign Key values simple. However, Synthetic Key values alone are no guarantee of the uniqueness of a record (beyond that of the key itself), so these tables will also require unique constraints on their Natural Keys to prevent duplicate records being added.

The final data model should ideally look something like this :

dvds_data_model

Fun with Synthetic Keys

The first tables we’re going to generate are the CATEGORY and SERIES reference tables.
As we’re using Django, we don’t need to type any SQL for this.
Instead, we need to go to the project directory and create a file called models.py.

So, if we’re using the installation I setup previously…

cd ~/dvds/dvds
nano models.py

…and now we can define the CATEGORY object like this :

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    category_name = models.CharField(max_length = 50)

    def __str__(self):
        return self.category_name

We now need to tell Django to implement (migrate) this definition to the database so…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

Now, if this were a common or garden Python article, we’d be heading over to the Python interactive command line (possibly via another Monty Python reference). The fact is though that I’m getting withdrawal symptoms from not writing any SQL so, we’re going to install a CLI for SQLite.
Incidentally, if hacking around on the command line is not your idea of “a big party”, you can always go down the route of obtaining an IDE for SQLite – SQLite Studio seems as good as any for this purpose.

If like me however, you regard the command line as an opportunity for “a wizard-jolly time”…

sudo apt-get install sqlite3

…and to access the CLI, we can now simply run the following :

cd ~/dvds
sqlite3 db.sqlite3

Django will have created the table using the application name as a prefix. So, in SQLite, we can see the DDL used to generate the table by running …

.schema dvds_category

The output (reformatted for clarity) is :

CREATE TABLE "dvds_category"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "category_name" varchar(50) NOT NULL
);

The problem with this particular table can be demonstrated easily enough (incidentally, a Blokebuster is the opposite of a Chick Flick, in case you’re wondering)…

insert into dvds_category(category_name) values ('BLOKEBUSTER');

insert into dvds_category(category_name) values ('BLOKEBUSTER');

select *
from dvds_category;

1|BLOKEBUSTER
2|BLOKEBUSTER

As is evident, the Unique Key on category_name has not been implemented. Without this, the Synthetic Key on the table (the ID column) does nothing to prevent the addition of what are, in effect, duplicate records.

After tidying up…

delete from dvds_category;
.quit

…we need to re-visit the Category class in models.py…

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

This time, we’ve told Django that category_name has to be unique as well. So, when we migrate our change…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

…and check the DDL that Django has used this time…

sqlite3 db.sqlite3
.schema dvds_category

…we can see that Django has added a Unique Constraint on the category_name…

CREATE TABLE "dvds_category"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "category_name" varchar(50) NOT NULL UNIQUE
);

…meaning that we now no longer get duplicate category_names in the table…

insert into dvds_category(category_name) values('BLOKEBUSTER');
insert into dvds_category(category_name) values('BLOKEBUSTER');
Error: UNIQUE constraint failed: dvds_category.category_name

It’s worth noting here that some RDBMS engines create a Unique Index to enforce a Primary Key. Were this the case for this table, you’d end up with two indexes on a two-column table. This is would not be the most efficient approach in terms of performance or storage.
Assuming that’s not a problem, we can move on and add the Series object to models.py as it’s structure is similar to that of Category…

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

…and deploy it…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

…which should result in a table that looks like this in SQLite :

CREATE TABLE "dvds_series"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "series_name" varchar(50) NOT NULL UNIQUE
);

Fun as it is messing around in the database on a command line, it’s not very frameworky…

DML using the Admin Interface

It’s a fairly simple matter to persuade Django to provide an interface that allows us to manage the data in our tables.
Step forward admin.py. This file lives in the same directory as models.py and, for our application as it stands at the moment, contains :

from django.contrib import admin
from .models import Category, Series

#Tables where DML is to be managed via admin
admin.site.register(Category)
admin.site.register(Series)

Save this and then just run :

./manage.py migrations

Now, if we run the server…

./manage.py runserver

…we can navigate to the admin site (appending /admin to the development server URL…

admin

You can then connect using the credentials of the super user you created when you setup Django initially.

Once connected, you’ll notice that Django admin has a bit of an issue with pluralising our table names

admin_wrong_plural

We’ll come back to this in a mo. First though, let’s add some Category records…

Click the Add icon next to “Categorys” and you’ll see …add_cat

Once we’ve added a few records, we can see a list of Categories just by clicking on the name of the table in the Admin UI :

cat_list

This list appears to be sorted by most recently added Category first. It may well be that we would prefer this listing to be sorted in alphabetical order.

We can persuade Django to implement this ordering for our tables, as well as correctly pluralizing our table names by adding a Meta class for each of the corresponding classes in models.py :

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

    class Meta :
        # set the default behaviour to be returning categories in alphabetical order by category_name
        ordering = ["category_name"]
        # Define the correct plural of "Category". Among other places, this is referenced in the Admin application
        verbose_name_plural = "categories"

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

    class Meta :
        ordering = ["series_name"]
        verbose_name_plural = "series"

Once we migrate these changes :

./manage.py makemigrations dvds
./manage.py migrate

…and restart the dev server…

./manage.py runserver

…we can see that we’ve managed to cure the Admin app of it’s speech impediment…

correct_plural

…and that the Category records are now ordered alphabetically…

cat_order

It’s worth noting that specifying the ordering of records in this way will cause an additional sort operation whenever Django goes to the database to select from this table.
For our purposes the overhead is negligible. However, this may not be the case for larger tables.

So far, we’ve looked at a couple of fairly simple reference data tables. Now however, things are about to get rather more interesting…

Foreign Keys and other exotic database constructs

The Title object (and it’s corresponding table) are at the core of our application.
Unsurprisingly therefore, it’s the most complex class in our models.py.

In addition to the Referential Integrity constraints that we need to implement, there are also the media_type and bbfc_certificate fields, which can contain one of a small number of static values.
We also need to account for the fact that Django doesn’t really do composite Primary Keys.
I’m going to go through elements of the code for Title a bit at a time before presenting the final models.py file in it’s entirety.

To start with then, we’ll want to create a couple of choices lists for Django to use to validate values for some of the columns in the Title table…

# Allowable values Lists
MEDIA_FORMAT_CHOICES = (
    ('BR', 'Blu Ray'),
    ('DVD', 'DVD'),
)

# British Board of Film Classification Certificates
# as per the official BBFC site - http://www.bbfc.co.uk
BBFC_CHOICES = (
    ('U', 'U'),
    ('PG', 'PG'),
    ('12A', '12A'),
    ('15', '15'),
    ('18', '18'),
    ('R18', 'R18'),
)

In a database, you would expect these valid values to be implemented by check constraints. Django however, goes it’s own way on this. I’d infer from the lack of resulting database constraints that the Choices Lists will work so long as you always populate/update your underlying tables via the Django application itself.
Incidentally, it is possible to reference these name/value pairs in Django templates should the need arise, something I will cover in a future post. It is for this reason that I’ve declared them outside of the classes in which they’re used here.

As with choices, the same appears to apply to the check we’ve added to ensure that we don’t get a silly value for the year a film was released, which necessitates …

from django.core.validators import MinValueValidator
...
year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.

Our first attempt at the Title class looks like this :

class Title( models.Model) :

    # For optional fields, blank = True means you can leave the field blank when entering the record details
    # null = True means that the column is nullable in the database
    title_name = models.CharField( max_length = 250)
    year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.
    bbfc_certificate = models.CharField("BBFC Certificate", max_length = 3, choices = BBFC_CHOICES)
    media_format = models.CharField("Format", max_length = 3, choices = MEDIA_FORMAT_CHOICES)
    director = models.CharField(max_length = 100, null=True, blank=True)
    synopsis = models.CharField( max_length = 4000, null = True, blank = True)
    series = models.ForeignKey( Series, on_delete = models.CASCADE, null = True, blank = True)
    number_in_series = models.IntegerField(null = True, blank = True)
    categories = models.ManyToManyField(Category, blank = True)

    class Meta :
        ordering = ["series", "number_in_series", "title_name"]

    def __str__(self) :
        return self.title_name

Hang on, haven’t I forgotten something here ? Surely I need some way of implementing the Natural Key on this table ?
You’re right. However this omission is deliberate at this stage, for reasons that will become apparent shortly.
Yes, this is part of a plan “so cunning you could brush your teeth with it”.

Even without this key element, there’s quite a lot going on here. In the main class :

  • the year_released cannot be before 1878
  • the bbfc_certificate and media_format columns are associated with their choices lists using the choices option
  • we’ve specified that series as type models.ForeignKey
  • we’ve specified categories as the somewhat intriguing type models.ManyToManyField

In the Meta class, we’ve stipulated a multi-column ordering clause. Note that the default ordering appears to put nulls last. Therefore Title records that have null series and number_in_series values will appear first.

When we plug this into our models.py and apply the changes…

./manage.py makemigrations dvds
./manage.py migrate

…then check in the database…

sqlite3 db.sqlite3
.tables dvds_title%
dvds_title             dvds_title_categories

…we can see that Django has created not one, but two new tables.

In addition to the DVDS_TITLE table, which we may have expected and which looks like this :

CREATE TABLE dvds_title (
    id               INTEGER        NOT NULL
                                    PRIMARY KEY AUTOINCREMENT,
    title_name       VARCHAR (250)  NOT NULL,
    year_released    INTEGER        NOT NULL,
    bbfc_certificate VARCHAR (3)    NOT NULL,
    media_format     VARCHAR (3)    NOT NULL,
    director         VARCHAR (100),
    synopsis         VARCHAR (4000),
    number_in_series INTEGER,
    series_id        INTEGER        REFERENCES dvds_series (id)
);

…Django has been smart enough to create a join table to resolve the many-to-many relationship between TITLE and CATEGORY :

CREATE TABLE dvds_title_categories (
    id          INTEGER NOT NULL
                        PRIMARY KEY AUTOINCREMENT,
    title_id    INTEGER NOT NULL
                        REFERENCES dvds_title (id),
    category_id INTEGER NOT NULL
                        REFERENCES dvds_category (id)
);

Whilst Django can’t resist slapping on a gratuitous Synthetic Key, it is at least clever enough to realise that a composite key is also required. To this end, it also creates an Unique Index on DVDS_TITLE_CATEGORIES :

CREATE UNIQUE INDEX dvds_title_categories_title_id_96178db6_uniq ON dvds_title_categories (
    title_id,
    category_id
);

So, it seems that Django can handle composite keys after all. Well, not quite.

Remember that we still need to add a unique key to TITLE as we’ve modelled it to have a Natural Key consisting of TITLE_NAME, YEAR_RELEASED and MEDIA_FORMAT.

We can do that easily enough, simply by adding a unique_together clause to Title’s Meta class in models.py :

class Meta :
    ordering = ["series", "number_in_series", "title_name"]
    # Natural Key for a Title record is title_name, year_released and media_format - we have some films on DVD AND Blu-Ray.
    unique_together = ('title_name', 'year_released', 'media_format',)

If we now apply this change…

./manage.py makemigrations dvds
./manage.py migrate

…we can see that Django has added the appropriate index…

CREATE UNIQUE INDEX dvds_title_title_name_ae9b05c4_uniq ON dvds_title (
    title_name,
    year_released,
    media_format
);

The really wacky thing about all this is that, if we had used the unique_together function in the first place, Django would not have created the Unique Key on the DVDS_TITLE_CATEGORIES table. However, as we’ve added Title’s Natural Key in a separate migration, Django leaves the Unique Key on DVDS_TITLE_CATEGORIES in place.
Irrespective of how practical the Synthetic Key on DVDS_TITLE may be, the fact is, it is defined as the Primary Key for that table. As DVDS_TITLE_CATEGORIES is a Join Table then, in relational terms, it should itself have a Natural Key consisting of the Primary Keys of the two tables it’s joining.

Anyway, our final models.py looks like this :

from django.db import models
from django.core.validators import MinValueValidator

# Allowable values Lists
MEDIA_FORMAT_CHOICES = (
    ('BR', 'Blu Ray'),
    ('DVD', 'DVD'),
)

# British Board of Film Classification Certificates
# as per the official BBFC site - http://www.bbfc.co.uk
BBFC_CHOICES = (
    ('U', 'U'),
    ('PG', 'PG'),
    ('12A', '12A'),
    ('15', '15'),
    ('18', '18'),
    ('R18', 'R18'),
)

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

    class Meta :
        # set the default behaviour to be returning categories in alphabetical order by category_name
        ordering = ["category_name"]
        # Define the correct plural of "Category". Among other places, this is referenced in the Admin application
        verbose_name_plural = "categories"

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

    class Meta :
        ordering = ["series_name"]
        verbose_name_plural = "series"

class Title( models.Model) :

    # For optional fields, blank = True means you can leave the field blank when entering the record details
    # null = True means that the column is nullable in the database
    title_name = models.CharField( max_length = 250)
    year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.
    bbfc_certificate = models.CharField("BBFC Certificate", max_length = 3, choices = BBFC_CHOICES)
    media_format = models.CharField("Format", max_length = 3, choices = MEDIA_FORMAT_CHOICES)
    director = models.CharField(max_length = 100, null=True, blank=True)
    synopsis = models.CharField( max_length = 4000, null = True, blank = True)
    series = models.ForeignKey( Series, on_delete = models.CASCADE, null = True, blank = True)
    number_in_series = models.IntegerField(null = True, blank = True)
    categories = models.ManyToManyField(Category, blank = True)

    class Meta :
        ordering = ["series", "number_in_series", "title_name"]
        # Natural Key for a Title record is title_name, year_released and media_format - we have some films on DVD AND Blu-Ray.
        unique_together = ('title_name', 'year_released', 'media_format',)

    def __str__(self) :
        return self.title_name

We also want to add Title to admin.py so that we can perform DML on the table in the admin application. Hence our final admin.py looks like this :

from django.contrib import admin
from .models import Category, Series, Title

#Tables where DML is to be managed via admin
admin.site.register(Category)
admin.site.register(Series)
admin.site.register(Title)
Conclusion

Django makes a pretty decent fist of implementing and maintaining a Relational Data Model without the developer having to write a single line of SQL.
Of course, as with any code generator, some of it’s design decisions may not be those that you might make if you were writing the code by hand.
So, if the data model and it’s physical implementation is important to your application, then it’s probably worth just checking up on what Django is up to in the database.


Filed under: python, SQL Tagged: admin.py, Django, foreign key, makemigrations, manage.py, migrate, models.py, Natural Key, runserver, SQLite, synthetic key, unique_together

Configuring Django with Apache on a Raspberry Pi

Tue, 2017-02-21 07:07

Deb has another job for me to do around the house.
She would like to have a means of looking up which Films/TV Series we have lying around on Blu-Ray or DVD so she can save time looking for films we haven’t actually got. Just to be clear, she doesn’t mind hunting around for the disc in question, she just wants to make sure that it’s somewhere to be found in the first place.
She wants to be able to do this on any device at any time (let’s face it, there’s even a browser on your telly these days).
As DIY jobs go, this is a long way from being the worst as far as I’m concerned. After all, this time I should be able to put something together without the potential for carnage that’s usually attendant when I reach for the toolbox.

I happen to have a Raspberry Pi lying around which should serve as the perfect hardware platform for this sort of low traffic, low data-volume application.
The Pi is running Raspbian Jessie.
Therefore, Python is the obvious choice of programming language to use. By extension therefore, Django appears to be a rather appropriate framework.
In order to store the details of each movie we have, we’ll need a database. Django uses with Sqlite as the default.

We’ll also need an HTTP server. Whilst Django has it’s own built-in “development” server for playing around with, the favoured production http server appears to be Apache.

Now, getting Django and Apache to talk to each other seems to get a bit fiddly in places so what follows is a description of the steps I took to get this working…leaving out all the bits where I hammered my thumb…

Other places you may want to look

There are lots of good resources for Django out there.
The Django Project has a a list of Django Tutorials.
One particularly good beginners tutorial, especially if you have little or no experience of programming, is the Django Girls Tutorial.

Making sure that Raspbian is up-to-date

Before we start installing the bits we need, it’s probably a good idea to make sure that the OS on the Pi is up-to-date.
Therefore, open a Terminal Window on the Pi and run the following two commands…

sudo apt-get update -y
sudo apt-get upgrade -y

This may take a while, depending on how up-to-date your system is.
Once these commands have completed, you’ll probably want to make sure you haven’t got any unwanted packages lying around. To achieve this, simply run :

sudo apt-get autoremove
Python Virtual Environments

Look, don’t panic. This isn’t the sort of Virtual Environment that requires hypervisors and Virtual Machines and all that other complicated gubbins. We’re running on a Pi, after all, we really haven’t got the system resources to expend on that sort of nonsense.
A Python virtual environment is simply a way of “insulating” your application’s Python dependencies from those of any other applications you have/are/will/may develop/run on the same physical machine.

Getting this up and running is fairly simple, but first, just as a sanity check, let’s make sure that we have Python 3 installed and available :

python3 --version

python3_version

Provided all is well, then next step is to install the appropriate Python 3 package for creating and running Virtual Environments so…

sudo pip3 install virtualenv

Next, we need to create a parent directory for our application. I’m going to create this under the home directory of the pi user that I’m connected as on the pi.
I’m going to call this directory “dvds” because I want to keep the name nice and short.
To create a directory under your home in Linux…

mkdir ~/dvds

You can confirm that the directory has been created in the expected location by running …

ls -ld ~/dvds

drwxr-xr-x 5 pi pi 4096 Feb 14 13:05 /home/pi/dvds

Now…

cd ~/dvds
virtualenv dvdsenv

…will create the python executables referenced in this environment :

virtualenv

Notice that this has created a directory structure under a new directory called dvdsenv :

dvdsenv

Now start the virtualenv and note what happens to the prompt :

source dvdsenv/bin/activate

virtual_prompt

One small but welcome advantage to running in your new environment is that you don’t have to remember the “3” whenever you want to run python. The easiest way to demonstrate this is to stop the virtual environment, get the python version, then re-start the virtual environment and check again, like this…

virtual_python

Installing Django

We want to do this in our newly created virtual environment.
So, if you’re not already in it, start it up :

cd ~/dvds
source dvdsenv/bin/activate

Now we use pip3 to get django. NOTE – as with the python command, we don’t need to remember the “3” for pip inside the virtual environment…

pip install django

django_install

Still in the Virtual environment, we can now create our new django project ( be sure to be in the dvds directory we created earlier) :

cd ~/dvds
django-admin.py startproject dvds .

Note the “.” at the end of this command. that means that the directory tree structure of the new application should be created in the current directory.

Once this has run, you should see a sub-directory called dvds :

django_dir

We now need to make some changes to some of the files that Django has created in this directory. To make these changes I’m going to use the default Raspbian graphical editor, Leafpad. If you’d prefer something like nano, then knock yourself out. Just replace “leafpad” with the executable name of your editor in the commands that follow…

leafpad ~/dvds/dvds/settings.py

We need to make a couple of changes to this file.
Firstly, in the INSTALLED_APPS section of the file (around about line 33) we want to add our application – dvds. After the change, this particular section of the file should look something like this :

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'dvds',
]

The other thing to do is to make sure that STATIC_ROOT has been defined. If this does not already exist in settings.py then add it at the end of the file :

STATIC_ROOT = os.path.join( BASE_DIR, "static/")

To get Django to accept these changes we need to migrate them. Note that we need to do this from inside the virtual environment so start it if it’s not already running…

cd ~/dvds
source dvdsenv/bin/activate
./manage.py makemigrations
./manage.py migrate

migrations

Before we finally get Django up and running, we need to setup the default admin UI.
To do this, we first need to create an admin user :

./manage.py createsuperuser

superuser

…then setup the static files used by the admin app…

./manage.py collectstatic

You have requested to collect static files at the destination
location as specified in your settings:

    /home/pi/dvds/static

This will overwrite existing files!
Are you sure you want to do this?

Type 'yes' to continue, or 'no' to cancel:

Type “yes” and you’ll get …

Copying '/home/pi/dvds/dvdsenv/lib/python3.4/site-packages/django/contrib/admin/static/admin/css/base.css'
Copying '/home/pi/dvds/dvdsenv/lib/python3.4/site-packages/django/contrib/admin/static/admin/css/widgets.css'
Copying '/home/pi/dvds/dvdsenv/lib/python3.4/site-packages/django/contrib/admin/static/admin/css/rtl.css'
...
Copying '/home/pi/dvds/dvdsenv/lib/python3.4/site-packages/django/contrib/admin/static/admin/js/admin/RelatedObjectLookups.js'

61 static files copied to '/home/pi/dvds/static'.

Now we can test that everything is working as expected by running Django’s own “development” http server :

./manage.py runserver

django_server

If we now point the Epiphany browser on the pi to that address, we should see the default Django page :

django_default

Better even than that, if you append “/admin” to the url – i.e.

http://127.0.0.1:8000/admin

You should see…

admin_login

Using the username and password you just created for with the “createsuperuser” command just now, you should get access to :

admin_page_new

Installing Apache

This is fairly straight forward, to start with at least.
First of all, you don’t need to be in the Python Virtual Environment for this so, if you are then deactivate it :

deactivate

Once this command has completed, the prompt should now return to normal.

I’ll be sure to tell you when you need the Virtual Environment again.

To install Apache…

sudo apt-get install apache2 -y

Once that’s completed, you should be able to confirm that Apache is up and running simply by pointing your browser to :

http://localhost

…which should display the Apache Default Page :

apache_default_page

In addition to Apache itself, we need some further packages to persuade Apache to serve pages from our Django application :

sudo apt-get install apache2-dev -y
sudo apt-get install apache2-mpm-worker -y
sudo apt-get install libapache2-mod-wsgi-py3 

Got all that ? Right…

Configuring Apache to serve Django Pages using WSGI

First of all, we need to tell Apache about our Django application. To do this we need to edit the 000-default.conf which can be found in the Apache directories :

leafpad /etc/apache2/sites-available/000-default.conf

We need to add some entries to the section of the file. Once we’re done, the entire file should look something like this :

<VirtualHost *:80>
	# The ServerName directive sets the request scheme, hostname and port that
	# the server uses to identify itself. This is used when creating
	# redirection URLs. In the context of virtual hosts, the ServerName
	# specifies what hostname must appear in the request's Host: header to
	# match this virtual host. For the default virtual host (this file) this
	# value is not decisive as it is used as a last resort host regardless.
	# However, you must set it for any further virtual host explicitly.
	#ServerName www.example.com

	ServerAdmin webmaster@localhost
	DocumentRoot /var/www/html

	# Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
	# error, crit, alert, emerg.
	# It is also possible to configure the loglevel for particular
	# modules, e.g.
	#LogLevel info ssl:warn

	ErrorLog ${APACHE_LOG_DIR}/error.log
	CustomLog ${APACHE_LOG_DIR}/access.log combined

	# For most configuration files from conf-available/, which are
	# enabled or disabled at a global level, it is possible to
	# include a line for only one particular virtual host. For example the
	# following line enables the CGI configuration for this host only
	# after it has been globally disabled with "a2disconf".
	#Include conf-available/serve-cgi-bin.conf

 Alias /static /home/pi/dvds/static
    <Directory /home/pi/dvds/static> 
        Require all granted
    </Directory>

    <Directory /home/pi/dvds/dvds>
        <Files wsgi.py>
            Require all granted
        </Files>
    </Directory>

    WSGIDaemonProcess dvds python-path=/home/pi/dvds python-home=/home/pi/dvds/dvdsenv
    WSGIProcessGroup dvds
    WSGIScriptAlias / /home/pi/dvds/dvds/wsgi.py
</VirtualHost>

# vim: syntax=apache ts=4 sw=4 sts=4 sr noet

Next, we need to make sure that Apache has access to the bits of Django it needs. To do this, we’ll give access to the group that the user under which Apache runs belongs to :

chmod g+w ~/dvds/db.sqlite3
chmod g+w ~/dvds
sudo chown :www-data db.sqlite3
sudo chown :www-data ~/dvds

After all of that, the “Apache” group (www-data) should be the group owner of the Virtual environment as well as our SQLITE database :

apache_privs

Finally, we need to re-start Apache for these changes to take effect :

sudo service apache2 restart

If we now go to the Apache url (http://localhost), we can see that it’s now showing the Django default page :

django_in_apache
If you see that then, congratulations, it works !

Accessing Django from another computer on the network

The server name of my Raspberry Pi is raspberrypi. If you want to check that this is the case for you, simply open a Terminal on the pi and run :

uname -n

In order to access the application from other computers on my local network, I’ll need to add this server name to the ALLOWED_HOSTS list in the settings.py file of the application.

To do this :

leafpad ~/dvds/dvds/settings.py

Amend the ALLOWED_HOSTS entry from this :

ALLOWED_HOSTS=[]

…to this…

ALLOWED_HOSTS=['raspberrypi']

And you should now be able to access the Django application from a remote machine by using the url :

raspberrypi

…like this…

remote_page_view

Hopefully, this has all helped you to get up and running without hitting your thumb.


Filed under: python Tagged: Apache, Django, Python virtual environment, Raspberry Pi

Breaking the Rules – why sometimes it’s OK to have a standalone PL/SQL Function

Mon, 2017-02-06 16:45

It was late. We were snuggled up on the sofa, watching a Romcom and debating whether to go to bed or see it through to the bitter( well, sickly sweet) end.

Wearily, I made the point that in the end the film would follow Heigl’s Iron Law of Romcom which can be summarised as “Katherine always gets her man”.

Deb begged to differ. Her argument was that, for every Colin Firth, riding into the sunset with his Bridget Jones, there’s a poor( largely blameless) Patrick Dempsey whose immediate future includes long-evenings alone in front of the telly and shopping for microwave meals for one.
The point is that even the most rigid rules tend to have their exceptions.

The star of this post is the oft-quoted rule that PL/SQL program units should always be incorporated into a Package.
There are special cameo appearances by “Never use Public Synonyms” and the ever popular “Never grant privileges to Public”.

Why Grouping Functions and Procedures in Packages is a Good Idea

“Always use a package. Never use a standalone procedure.”
This is a quote from Tom Kyte.
More precisely, it’s a partial quote. We’ll come back to that in a moment.

Mr Kyte goes on to expound the virtues of packages because they ( quoting once again)…

“- break the dependency chain (no cascading invalidations when you install a new package body — if you have procedures that call procedures — compiling one will invalidate your database)

– support encapsulation — I will be allowed to write MODULAR, easy to understand code — rather then MONOLITHIC, non-understandable procedures

– increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding

– support overloading

– support session variables when you need them

– promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together….

Well that all seems fairly comprehensive. So why are we even having the discussion ? Well, it comes back to the rest of the above quote, which tends to get missed when rules like this are invoked.

The full quote is actually :

“Always use a package. Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing).”

Having recently covered the fact that “unless your writing tests” should be appended to any rule relating to Oracle code, I’m going to focus on…

An Application-Independent Standalone function

It just so happens that I have one of these lying around
The radix_to_decimal function takes a string representation of a number in a base between 2 and 36 and returns it’s decimal equivalent.
The function does not read from or write to any application tables :

create or replace function radix_to_decimal( i_number in varchar2, i_radix in pls_integer)
    return pls_integer
--
-- Function to return the decimal representation of i_number in i_radix.
-- This handles bases between 2 and 36 (i.e. any base where numeric values are represented by alphanumeric characters)
--
is
    ASCII_0 constant pls_integer := 48; -- the ascii value for '0'
    ASCII_9 constant pls_integer :=  57; -- the ascii value for '9'

    revnum varchar2(38);
    rtnval pls_integer := 0;
    digit varchar2(1);

    e_missing_param_value exception;
    e_invalid_radix exception;
    e_invalid_digit_for_base exception;
begin
    -- Parameter sanity checks
    if i_number is null or i_radix is null then
        raise e_missing_param_value;

    elsif i_radix not between 2 and 36 then
        raise e_invalid_radix;

    elsif i_radix = 10 then
        return i_number;

    -- Validate that i_number is actually a valid i_radix value.
    elsif (i_radix > 10 and instr( i_number, chr(55 + i_radix),1,1) > 0)
        or ( i_radix < 10 and instr( i_number, i_radix, 1, 1) > 0) 
    then
            raise e_invalid_digit_for_base;
    end if;

    -- Reverse the i_number string so we can loop through and sum the decimal numbers represented by each character
    -- without having to check the length of i_number.
    -- The REVERSE function is a SQL, rather than PL/SQL built-in, hence...

    select reverse(i_number) into revnum from sys.dual;

    for i in 1..length(revnum) loop
        digit := substr(revnum, i, 1);
        if ascii(digit) between ASCII_0 and ASCII_9 then
            rtnval := rtnval + ( digit * power(i_radix, i - 1));
        else
            -- letters in bases above 10 are always offset from 10 - e.g. A = 10, B = 11 etc.
            -- so, subtracting 55 from the ascii code of the upper case letter will give us the decimal value
            rtnval := rtnval + ( ( ascii( upper( digit)) - 55) * power( i_radix, i - 1) );
        end if;
    end loop;
    return rtnval;

exception

    when e_missing_param_value then
        raise_application_error( -20000, 'Both a number and a base must be specified');

    when e_invalid_radix then
        raise_application_error( -20001, 'This function only converts bases 2 - 36');

    when e_invalid_digit_for_base then
        raise_application_error( -20002, 'Number '||i_number||' is not a valid '||i_radix||' number.');

end radix_to_decimal;
/

Here’s a quick demo of the function in action….

select radix_to_decimal('101', 2) from dual;

RADIX_TO_DECIMAL('101',2)
-------------------------
                        5

select radix_to_decimal('401', 8) from dual;

RADIX_TO_DECIMAL('401',8)
-------------------------
                      257

select radix_to_decimal('7E0', 16) from dual;

RADIX_TO_DECIMAL('7E0',16)
--------------------------
                      2016

I’ve also uploaded the function to LiveSQL so feel free to have a play around with it.

Meanwhile, back in the database, to make this function generally available, grant execute to everyone…

grant execute on radix_to_decimal to public
/

What’s that ? I’ve violated the principle of least privilege ? Well, you may have a point. However, that principle has been weighed against the practicality of being able to re-use this code ( the principle of Don’t Repeat Yourself).
Whilst, under most circumstances, security wins out, there are (in Oracle at least) one or two exceptions as you can see by running…

select count(*)
from all_tab_privs
where privilege = 'EXECUTE'
and grantee = 'PUBLIC'
/

In order to make it easy to call this function, we don’t want to have to remember which schema we happened to put this in, so we’re going to create a public synonym…

create or replace public synonym radix_to_decimal for mike.radix_to_decimal
/

Once again, you may raise the very valid issue of namespace pollution caused by the use of Public Synonyms.
Once again, I’ve chosen pragmatism over principle in this specific instance.
Of course, if the next version of oracle contains a function called radix_to_decimal you can come back and say “I told you so !”


Filed under: Oracle, PL/SQL Tagged: grant to public, LiveSQL, public synonyms, radix_to_decimal, Standalone Function

Automated Testing Frameworks and General Rule-Breaking in PL/SQL

Sat, 2017-01-07 08:58

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…

Writing Unit Tests means Breaking the Rules

OK, so maybe that should be “incomplete” rather than wrong.

As well as general “golden rules” that govern good programming practice, each language will have it’s own specific rules. These rules are usually along the lines of “Never do x” or “Always do Y”.
Leaving aside the problems inherent of using the words “Always” and “Never” in this context, I am now of the opinion that they should normally end with the words “…unless you’re writing a Unit Test”.

Reviewing the test code I’ve written over the last few months offers numerous examples of this.
Of course, it could just be down to the quality of the programmer but…

Always use Bind Variables

This is a standard in PL/SQL for very good reasons. Bind variables not only offer significant performance advantages, they serve to protect against the injection of malicious code. However, whilst I found myself rather uncomfortable about writing this code in Ruby-plsql-spec…

def get_tournament_rec( i_comp_code, i_year_completed, i_host_nation)
# Return the record for a tournament
# Alternative version concatenating arguments into a string...
    l_stmnt = "
        select id, comp_code, year_completed, host_nation, year_started, number_of_teams
        from footie.tournaments
        where comp_code = '#{i_comp_code}'
        and year_completed = #{i_year_completed}
        and host_nation "
         
    if i_host_nation.nil? then
        l_stmnt = l_stmnt + "is null"
    else
        l_stmnt += " = '#{i_host_nation}'"
    end
        plsql.select_first l_stmnt
end

…this equivalent example using SQLDeveloper Unit Testing seemed perfectly fine…

select null
from competitions
where comp_code = '{I_CODE}'

…even in utPLSQL, we find stuff like …

procedure ut_update_no_of_teams
is
begin
	-- Execute
	footie.manage_tournaments.edit_tournament(i_id => g_id, i_teams => 16, i_year_start => null);
	-- Validate
	utAssert.eqQueryValue
	(
		'Confirm Number of Teams Updated',
		'select count(*) from footie.tournaments where id = '||g_id||' and number_of_teams = 16 and year_started is null',
		1
	);
end ut_update_no_of_teams;

Seeing these constructs in Application code would start alarm bells ringing. So why then, are they apparently OK in Unit Tests ?

Well, first of all, performance is not necessarily as crucial an issue for Unit Tests as it might be for the Application itself. You may well be able to live with the odd additional hard-parse in your test suite if it means writing a bit less test code.

From a security perspective, whilst it’s still prudent to be wary of concatenating user input into executable statements, in all of the above instances, the variables in question do not contain user supplied values. They are either generated at runtime or are hard-coded.

Wait, that’s not right is it ? I mean you’re supposed to avoid hard-coding values right ?

Do not hard-code values

The fundamental purpose of a unit test could be summarised as :

With an application in a known state, a known input will result in a known output.

That’s an awful lot of stuff that you need to know in order to execute a Unit Test and to then verify the output.
With this in mind, it’s not surprising that this sort of thing becomes commonplace in Unit Test Code :

create or replace package body  utp_footie.ut_edit_tournament 
as
	g_code footie.competitions.comp_code%type := 'EURO';
	g_year_end footie.tournaments.year_completed%type := 2016;
	g_host footie.tournaments.host_nation%type := 'FRANCE';
	g_teams footie.tournaments.number_of_teams%type := '24';
	g_year_start footie.tournaments.year_started%type := 2013;
,
...

Perhaps even more controversial in PL/SQL circles is code that contravenes the rule that says…

A When Others Exception not followed by a RAISE is a bug

In testing terms, my evidence for having this commandment re-worded is…

    procedure ut_add_tourn_start_after_end
    is
        l_err_code number := 0;
        l_year_start footie.tournaments.year_started%type := 1918;
    begin
        footie_ut_helpers.ensure_comp_exists(g_code);
        begin
            footie.manage_tournaments.add_tournament
            (
                i_code => g_code,
                i_year_end => g_year_end,
                i_teams => g_teams,
                i_year_start => l_year_start
            );
        exception when others then
            l_err_code := sqlcode;
        end;
        utAssert.this('Cannot add a tournament that ends before it starts', l_err_code = -20000);
        rollback;
    end ut_add_tourn_start_after_end;

In this instance of course, we want to compare the error we actually get with the (hard-coded) error we were expecting.

One more illustration of exactly why a healthy disregard for rules is an asset when writing unit tests…

Any database interaction should be via a PL/SQL API

This is an approach that I’m particularly fond of, having had rather too much experience of applications where this architecture was not followed. However, if you’re writing a test for your PL/SQL API in a language that isn’t PL/SQL then something like this seems to be perfectly reasonable :

...
    expect( 
        plsql.footie.competitions.select( :all, "order by comp_code")
    ).to eq @expected
...

Now I’ve got that out of the way, it’s time to compare the frameworks in detail, starting with :

What these frameworks have in common

The first thing you may notice is that they are all similarly priced. That is to say that they are all free.
Both utPLSQL and Ruby-plsql-spec are Open Source.
SQLDeveloper, of which SQLDeveloper Unit Testing is an integral part, is also available at no cost.

As the first framework to be developed for PL/SQL, it is perhaps not surprising that utPLSQL has provided a template which the other frameworks have followed. This template itself, originated from JUnit.

In simple terms, a Unit Test consists of up to four phases :

  • Setup – any steps necessary to ensure that the Application is in a known state
  • Execute – run the code that is being tested
  • Validate – check that the actual results were what was expected
  • Teardown – any steps necessary to return the Application to it’s original state prior to the test being run

In terms of their capabilities, all of the Frameworks facilitate testing of scenarios that are commonly found in PL/SQL applications :

  • ability to test DML actions against a data model fully implementing Referential Integrity
  • ability to test DDL statements
  • ability to handle both OLTP and Data Warehouse style operations – including test steps that cross transaction boundaries
  • Ability to handle IN/OUT Ref Cursors

Additionally, they also share characteristics such as :

  • ability to run tests singly or as a larger suite
  • tests can be saved into discrete files and are therefore amenable to being stored in a Source Code Repository
  • possible (with varying degrees of configuration) to incorporate tests into a Continuous Integration tool

In short, any of the frameworks I’ve covered here will do the basics when it comes to Unit Testing your PL/SQL code. Their main distinguishing characteristics lie in their architecture…

SQLDeveloper Unit Testing

SQLDeveloper Unit Testing (SUT) is at the pointy-and-clicky end of the scale.
Typically for a declarative tool, there’s a bit of typing to start with but this reduces quickly once you start to add code to the Library.
Whilst SUT does require a database to house it’s repository objects, the fact that it’s built-in to SQLDeveloper means that the repository objects (and the tests themselves) can be separated completely from the database that holds the code to be tested.
The tests can be saved into text files (xml) and therefore placed under source control like any other text file.
If you want to execute SUT tests in a Continuous Integration environment, that environment will need to have SQLDeveloper installed. The tests themselves, can be executed using the SQLDeveloper CLI.

SUT is likely to be appealing as the PL/SQL testing framework of choice if :

  • SQLDeveloper is your standard Oracle IDE
  • You want to get up and running quickly with your TDD effort
  • You want to maintain a separation between test and application code in the RDBMS
utPLSQL

Unsurprisingly, utPLSQL is at the opposite end of the spectrum.
As with SUT, the repository lives in the database. However, the actual tests themselves are PL/SQL packages and are therefore likely to exist right alongside the application code being tested.
Of course, it’s perfectly possible to maintain some distinction and ease administration by ensuring that the tests are located in a separate schema.
Issues of Source Control and CI don’t really arise as the infrastructure required for utPLSQL is the same for that of the PL/SQL application code that you’re testing.

utPLSQL may come up for consideration because :

  • it is (probably) the most widely used PL/SQL testing framework
  • it has no dependency on any software that you don’t already have up and running
  • it provides a greater degree of control than SUT as all tests are pretty much hand-written
  • SQLDeveloper is not your default IDE
Ruby-plsql-spec

Ruby has long been a favourite language when writing unit tests. The RSpec framework, upon which ruby-plsql-spec is based is highly regarded by many who make their living writing code outside of the database.
Unlike the other two frameworks, ruby-plsql-spec does not need to create any objects in any Oracle database.
All it needs is a means of connecting to the database holding the application code you want to test.
In terms of pre-requisites for executing tests in a CI envioronment, all you need is ruby itself ( which comes as standard in most Linux Distros), and the Ruby Gems that we installed for the client.

Ruby-plsql-spec is a contender when :

  • You’re already testing other technologies in your application using Ruby
  • You want a complete separation between test and application code
  • SQLDeveloper is not your default IDE
  • The overhead of writing more code is offset by either/both of the above
  • You’re happy to sacrifice some of the flexibility offered by the fact that the other frameworks are native to Oracle ( e.g. Merge statements)

It’s clear that each of these frameworks have their own particular strengths and weaknesses, but which one would I choose ?

If the Fence is Strong Enough, I’ll Sit on It

If you ask me which of these frameworks I’ll use going forward, the answer is all of them.

For my Footie application, which served as a Guinea Pig for the framework evaluation, I’m going to stick with SUT.
I use SQLDeveloper and using the declarative tools mean that I’ll spend much less time coding tests and more time doing the fun stuff.

As far as utPLSQL is concerned, not only is it highly likely that I’ll find myself working on a project where this is used as the standard, there is a major overhaul under way to bring utPLSQL up to date.
utPLSQL Version 3.0 may well be a game-changer.

As for Ruby-plsql-spec, that’s the framework that we use for the crudo application. I’m in the process of adding more tests using this framework ( or will be when I finish writing this).

Conclusion

Whatever approach to Unit Testing and/or Test Driven Development that appeals, there is a framework freely available for PL/SQL.
Whilst introducing tests to an existing code base can be challenging, adopting one of these a the start of a new project could well lead to a saving in time and effort down the line.


Filed under: Oracle, PL/SQL, SQL, SQLDeveloper Tagged: bind variables, ruby-plsql-spec, SQLDeveloper Unit Testing, utPLSQL, when others exception

Post-Truth PL/SQL

Fri, 2016-12-09 15:19

We’re living in a Post-truth age. I know this because I read it in my Fake News Feed.
Taking advantage of this, I’ve updated the definition of PL/SQL.
Up until now, it would be true to say that PL/SQL is a 3GL based on ADA that’s incorporated into the Oracle RDBMS.
Post truth, the definition is that PL/SQL is a 3GL that comes with it’s own built-in Oracle RDBMS.

By a stroke of good fortune, my son recently bought me a copy of Ghost in the Wires by Kevin Mitnick and William L Simon, which begins each chapter with an encrypted phrase.
If your anything like me, you’d spend a fair amount of time geeking over this sort of problem, most likely using some fashionable programming language to help solve the riddles with which you were presented.

In my house at least, PL/SQL is back in fashion…

Some true (and post-true) statements

The code presented here has been written on and tested on an Oracle 11g Express Edition database.
The purpose of this exercise is to demonstrate the power and utility of PL/SQL as a language in it’s own right. Yes, I make use of the database. To ignore it would be a bit like trying to write C without using pointers.

The point is that PL/SQL is not simply another stored procedure language, SQL tricked out to make it Turing Complete. It’s a proper 3GL in it’s own right.

The PL/SQL “standards” I’ve adopted here are intended for aesthetic reasons as well as those of readability. In other words, not using “l_” as a prefix for a local variable makes the code look less spikey.

I’ve followed the C convention with constants, which I’ve defined and used in uppercase.
I still can’t bring myself to use Camel Case in PL/SQL for reasons which I go into here.
If I look back at this code in six months time and try to figure out just what I was doing I’ll know whether the “aesthetic” standards, was a good idea.

The cryptograms contained in this post are taken from the Hardback edition of the book. The ones in the paperback are different.

Before we go any further, I would like it to be known that I managed to solve all of the encrypted phrases without any help at all. I did this by googling “Ghost in the Wires encrypted phrases” and then read the comprehensive solution offerred by Fabien Sanglard.
In the old days, this may have been considered akin to “looking in the back of the book for the answers”. As we’ve already established, times have changed…

The Caesar Cipher

Let’s take a look at the first encrypted phrase in the book :

yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?

Pretending that I haven’t already “looked in the back of the book” for the answers, we can deduce a couple of things from the way this phrase is formatted.

The fact that the letter groupings separated by spaces are not a constant number would seem to suggest that these are possibly words.
The question mark at the end would seem to re-enforce this notion and tell us that the encrypted phrase is a question.
This being the case, the repeating three letter pattern “vjg” might represent the word “THE”.
If this were the case, then it would mean that all of the letters in the phrase had been shifted forwards by 2 letters in the alphabet. Thus, V = T, J = H and G = E.
This method of encoding is known as a Caesar Cipher.

If we’re going to “have a stab” at cracking a Caesar Cipher then brutus.sql would seem to be an appropriate name for the following program…

set serveroutput on size unlimited
declare
    UPPER_A constant pls_integer := 65; -- Ascii code for 'A'
    UPPER_Z constant pls_integer := 90; -- Ascii code for 'Z'
    LOWER_A constant pls_integer := 97; -- Ascii code for 'a'

    phrase varchar2(4000); 
    offset pls_integer;
    this_char varchar2(1);
    decrypted_char varchar2(1);
    decrypted_phrase varchar2(32767);
    ascii_a pls_integer;
begin
    
    phrase := 'yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?';
    offset := 2;

    for i in 1..length( phrase) loop
        this_char := substr( phrase, i, 1);
        if ascii( upper( this_char)) not between UPPER_A and UPPER_Z then
            -- not a letter, just use the character unchanged
            decrypted_char :=  this_char;
        else
            -- Make sure that the character stays within the bounds of
            -- alpha ascii codes after the offset is applied
            ascii_a := case when ascii(this_char) between UPPER_A and UPPER_Z then UPPER_A else LOWER_A end;

            -- now apply the offset...
            decrypted_char := chr( ascii( this_char) - offset);

            if ascii(decrypted_char) < ascii_a then
                -- character needs to "wrap around" to the other end of the alphabet
                decrypted_char := chr( ascii(decrypted_char) + 26);
            end if;
        end if;
        decrypted_phrase := decrypted_phrase||decrypted_char;
    end loop;
    dbms_output.put_line( decrypted_phrase);
end;
/

Using the fact that uppercase letters have ASCII codes between 65 and 90, we can apply the offset by easily enough by subtracting the offset from the ASCII code of each letter then converting the result back into a character.
The ASCII function shows an ASCII code for a given character. The CHR function converts an ASCII value to it’s corresponding character.
In pre-truth terms, these are both SQL, rather than PL/SQL functions. However, SQL is merely a component in the Oracle RDBMS and therefore a subset of the all-encompassing Post Truth PL/SQL.

Leaving the semantics aside, when we run this we get :

@brutus.sql
what is the name of the system used by ham operators to make free phone calls ?

PL/SQL procedure successfully completed.

… so our hypothesis is correct.

One down, 37 to go. Whilst the script we’ve got at the moment is fine for cracking a single code, we could probably do with something a bit more parameterized…

Brute Forcing with Brutus

As you may have observed already, the Caesar Cipher is rather susceptible to brute forcing as the offset used to encode a phrase can only be up to 25.
OK, you can offset to 26 (the number of letters in the English alphabet) but then your encoded string will be the same as the unencoded one which rather defeats the whole object of the exercise.

To start with then, we can make things a bit easier for ourselves by persisting the phrases we want to crack. We’ll also want to record the plain text for each of them once we’ve managed to figure it out. Hang on, we’ve got a database lying around somewhere…

create table giw_codes
(
    chapter_no number(2) not null,
    cryptogram varchar2(4000) not null,
    encryption_method varchar2(50),
    message varchar2(4000),
    answer varchar2(4000),

    constraint giwc_pk primary key (chapter_no)
)
/

comment on table giw_codes is 'Cryptograms from the book Ghost in the Wires by Kevin Mitnick with William L. Simon. Table Alias : GIWC'
/

comment on column giw_codes.chapter_no is 'The chapter number in which the cryptogram appears. Part of Primary Key'
/

comment on column giw_codes.cryptogram is 'The encrypted phrase'
/

comment on column giw_codes.encryption_method is 'Method of encryption used'
/

comment on column giw_codes.message is 'The deciphered message'
/

comment on column giw_codes.answer is 'The answer to the question in the deciphered message.'
/

Now to populate it. Post-Truth PL/SQL still allows you to be a bit lazy with your inserts

declare

    -- because I can't be bothered to type the insert statement 38 times...

    procedure ins( i_chapter giw_codes.chapter_no%type, i_cryptogram giw_codes.cryptogram%type)
    is
    begin
        insert into giw_codes( chapter_no, cryptogram, encryption_method, message, answer)
        values( i_chapter, i_cryptogram, null, null, null);
    end ins;
    
begin
    -- ... I'll just call the procedure...
    
    ins(1, 'yjcv ku vjg pcog qh vjg uauvgo wugf da jco qrgtcvqtu vq ocmg htgg rjqpg ecnnu ?');
    ins(2, 'wbth lal voe htat oy voe wxbirtn vfzbqt wagye C poh aeovsn vojgav ?');
    ins(3, 'Nyrk grjjnfiu uzu Z xzmv kf jvklg re rttflek fe Kyv Rib ?');
    ins(4, q'[Flle ujw esc wexp mo xsp kjr hsm hiwwcm, 'Wplpll stq lec qma e wzerg mzkk!' ?]');
    ins(5, 'Bmfy ytbs ini N mnij tzy ns zsynq ymj Ozajsnqj Htzwy qtxy ozwnxinhynts tajw rj ?');
    ins(6, 'Kyoo olxi rzr Niyovo Cohjpcx ojy dn T apopsy ?');
    ins(7, 'Kvoh wg hvs boas ct hvs Doqwtwq Pszz sadzcmss kvc fsor hvs wbhsfboz asac opcih am voqywbu oqhwjwhwsq cjsf hvs voa forwc ?');
    ins(8, 'Iwh xwqv wpvpj fwr Vfvyj qks wf nzc ncgsoo esg psd gwc ntoqujvr ejs rypz nzfs ?');
    ins(9, 'Hsle td esp epcx qzc dzqehlcp mfcypo zy esp nsta esle Yzglepw dpye xp ?');
    ins(10, 'Bprf cup esanqneu xmm gtknv amme U biiwy krxheu Iwqt Taied ?');

    ins(11, 'Lwpi idlc sxs bn upiwtg axkt xc lwtc X bdkts xc lxiw wxb ?');
    ins(12, q'[Yhlt xak tzg iytfrfad RanBfld squtpm uhst uquwd ce mswf tz wjrwtsr a wioe lhsv Ecid mwnlkoyee bmt oquwdo't ledn mp acomt ?]');
    ins(13, 'Zkdw lv wkh qdph ri wkh SL ilup wkdw zdv zluhwdsshg eb Sdflilf Ehoo ?');
    ins(14, 'Plpki ytw eai rtc aaspx M llogw qj wef ms rh xq ?');
    ins(15, 'Ituot oaybmzk ymwqe ftq pqhuoq ftmf Xqiue geqp fa buow gb mzk dmpua eusmxe zqmd Qduo ?');
    ins(16, q'[Kwth qzrva rbq lcq rxw Svtg vxcz zm vzs lbfieerl nsem rmh dg ac oef'l cwamu ?]');
    ins(17, 'Epib qa bpm vium wn bpm ixizbumvb kwuxtmf epmzm Q bziksml lwev Mzqk Pmqvh ?');
    ins(18, 'Khkp wg wve kyfcqmm yb hvh TBS oeidr trwh Yhb MmCiwus Wko ogvwgxar hr ?');
    ins(19, q'[Rcvo dn ivhz ja ocz omvinvxodji oj adiy v kzmnji'n njxdvg nzxpmdot iphwzm pndib oczdm ivhz viy yvoz ja wdmoc ?]');

    ins(20, q'[Wspa wdw gae ypte rj gae dilan lbnsp loeui V tndllrhh gae awvnh 'HZO, hzl jaq M uxla nvu']');
    ins(21, '4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F ');
    ins(22, 'Gsig cof dsm fkqeoe vnss jo farj tbb epr Csyvd Nnxub mzlr ut grp lne ?');
    ins(23, 'Fqjc nunlcaxwrl mnerln mrm cqn OKR rwcnwcrxwjuuh kanjt fqnw cqnh bnjalqnm vh jyjacvnwc rw Ljujkjbjb ?');
    ins(24, 'Xvof jg qis bmns lg hvq thlss ktffb J cifsok EAJ uojbthwsbhlsg ?');
    ins(25, 'Cngz zuct ngy znk grsg sgzkx lux znk xkgr Kxoi Ckoyy ?');
    ins(26, 'Aslx jst nyk rlxi bx ns wgzzcmgw UP jnsh hlrjf nyk TT seq s cojorpdw pssx gxmyeie ao bzy glc ?');
    ins(27, '85 102 121 114 32 103 113 32 114 102 99 32 108 121 107 99 32 109 100 32 114 102 99 32 122 109 105 113 '
        ||'114 109 112 99 32 71 32 100 112 99 111 115 99 108 114 99 98 32 103 108 32 66 99 108 116 99 112 63');
    ins(28, 'Phtm zvvvkci sw mhx Fmtvr VOX Ycmrt Emki vqimgv vowx hzh L cgf Ecbst ysi ?');
    ins(29, '126 147 172 163 040 166 172 162 040 154 170 040 157 172 162 162 166 156 161 143 040 145 156 161 '
        ||'040 163 147 144 040 115 156 165 144 153 153 040 163 144 161 154 150 155 172 153 040 162 144 161 165 '
        ||'144 161 040 150 155 040 122 172 155 040 111 156 162 144 077');

    ins(30, q'[Ouop lqeg gs zkds ulv V deds zq lus DS urqstsn't wwiaps ?]');
    ins(31, 'Alex B25 rixasvo hmh M ywi xs xli HQZ qemrjveqi ?');
    ins(32, q'[Caem alw Ymek Xptq'd tnwlchvw xz lrv lkkzxv ?]');
    ins(33, 'Ozg ojglw lzw hshwj gf AH Khggxafy lzsl BKR skcww ew stgml ?');
    ins(34, q'[Nvbx nte hyv bqgs pj gaabv jmjmwdi whd hyv UVT'g Giuxdoc Gctcwd Hvyqbuvz hycoij ?]');
    ins(35, '2B 2T W 2X 2Z 36 36 2P 36 2V 3C W 3A 32 39 38 2Z W 3D 33 31 38 2V 36 3D W '
        ||'2R 2Z 3C 2Z W 3E 3C 2V 2X 2Z 2Y W 3E 39 W 2R 32 2V 3E W 2V 3A 2V 3C 3E 37 2Z 38 3E '
        ||'W 2X 39 37 3A 36 2Z 2S 1R');
    ins(36, 'Lsar JSA cryoi ergiu lq wipz tnrs dq dccfunaqi zf oj uqpctkiel dpzpgp I jstcgo cu dy hgq ?');
    ins(37, 'V2hhdCBGQkkgYWdlbnQgYXNrZWQgU3VuIE1pY3Jvc3lzdGVtcyB0byBjbGFpbSB0aGV5IGxvc3QgODAgbWlsbGlvbiBkb2xsYXJzPw==');
    ins(38, '100-1111-10-0 011-000-1-111 00-0100 1101-10-1110-000-101-11-0-1 '
        ||'0111-110-00-1001-1-101 111-0-11-0101-010-1-101 111-10-0100 110011');


    commit;
        
end;
/

Now, whilst we could modify our Brutus script to simply select the encrypted phrases from the table and just loop through all possible offsets for each of them, that would result in the output of 950 phrases, almost all of which would be gibberish. It would be good then, if we could persuade the program to be a bit more discerning about it’s output.

According to Wikipedia, the 25 most common words make up about one third of all printed material in English. So, by searching for these, we can potentially filter out most of the junk from our result set.
Another point to consider is that many of the messages we’re trying to decrypt appear to be questions, so throwing in some common ‘question’ words ( e.g. how, why, who, what etc) may be helpful.
I’m going to exclude ‘I’ and ‘A’ from this list as matching on them is likely to generate a lot of false positives.
In the end, what I’m left with then (using the boringly conventional method of inserting rows to a table) is…

-- Creating this as an Index Organized Table ensures that no words are duplicated
-- whilst avoiding the overhead of copying the entire contents of the table to an index.

create table common_words( word varchar2(30), constraint cw_pk primary key(word))
    organization index
/

insert into common_words( word) values('THE');
insert into common_words( word) values('BE');
insert into common_words( word) values('TO');
insert into common_words( word) values('OF');
insert into common_words( word) values('AND');
insert into common_words( word) values('IN');
insert into common_words( word) values('THAT');
insert into common_words( word) values('HAVE');
insert into common_words( word) values('IT');
insert into common_words( word) values('FOR');
insert into common_words( word) values('NOT');
insert into common_words( word) values('ON');
insert into common_words( word) values('WITH');
insert into common_words( word) values('HE');
insert into common_words( word) values('AS');
insert into common_words( word) values('YOU');
insert into common_words( word) values('DO');
insert into common_words( word) values('AT');
insert into common_words( word) values('THIS');
insert into common_words( word) values('BUT');
insert into common_words( word) values('HIS');
insert into common_words( word) values('BY');
insert into common_words( word) values('FROM');

-- Throw in some lexemes (whatever they are)...

insert into common_words( word) values('IS');
insert into common_words( word) values('WERE');
insert into common_words( word) values('WAS');
insert into common_words( word) values('SHE');
insert into common_words( word) values('HERS');
insert into common_words( word) values('THEIRS');

-- And some 'question' words...

insert into common_words( word) values('WHO');
insert into common_words( word) values('WHAT');
insert into common_words( word) values('HOW');
insert into common_words( word) values('WHERE');
insert into common_words( word) values('WHEN');
insert into common_words( word) values('WHY');

-- Add past tense of the verb to do...
insert into common_words(word) values('DID');

-- and a conditional...possibly not as gramatically correct as it should be but hey...
insert into common_words(word) values('IF');
insert into common_words(word) values('ELSE');
insert into common_words(word) values('DOES');
insert into common_words(word) values('WHILE');

-- and whatever other random stuff seems reasonable...
insert into common_words(word) values('WE');
insert into common_words(word) values('US');
insert into common_words(word) values('THEM');
insert into common_words(word) values('THEIR');
insert into common_words(word) values('OUR');

commit;

Now we’ve persisted our encrypted phrases and some of the most common English words, we can save ourselves a fair bit of typing by incorporating the required code into a package.

First we need to create a package header to define the signature of the public package members (functions and procedures that are available to callers from outside of the package) :

create or replace package decrypt as
    --
    -- utilities for decrypting the cryptograms in the GIW_CODES table.
    --

    -- These constants were originally in the brutus (now caesar) function.
    -- However, I have a funny feeling that they may be needed elsewhere...

    UPPER_A constant pls_integer := 65; -- Ascii code for 'A'
    UPPER_Z constant pls_integer := 90; -- Ascii code for 'Z'
    LOWER_A constant pls_integer := 97; -- Ascii code for 'a'

    -- If i_string contains at least i_num_matches common words then return true
    function has_common_words( i_string in varchar2, i_num_matches in pls_integer default 2)
        return boolean;

    -- update GIW_CODES with any decrypted messages.
    procedure save_decrypted
    (
        i_chapter_no giw_codes.chapter_no%type,
        i_encryption_method giw_codes.encryption_method%type,
        i_message giw_codes.message%type
    );

    -- Call this function to decrypt a single cryptogram generated from a single cipher with a known offset
    function caesar( i_cryptogram in giw_codes.cryptogram%type, i_offset in pls_integer)
        return giw_codes.message%type;

    -- For the impatient, this will attempt to decrypt all cryptograms in the GIW_CODES table...
    procedure brute_force_caesar;
end decrypt;
/

…now for the code itself, which is in the package body…

create or replace package body decrypt as

    function has_common_words( i_string in varchar2, i_num_matches in pls_integer default 2)
        return boolean
    is
    --
    -- check i_string for whole words in COMMON_WORDS.
    -- if we get i_num_matches then return true
    --
    
        SPACE constant varchar2(1) := chr(32);
        matches pls_integer := 0;
        
    begin
        for r_words in (select SPACE||word||SPACE as word from common_words) loop
            if instr( upper( i_string), r_words.word, 1, 1) > 0 then
                matches := matches + 1;
                if matches = i_num_matches then
                    return true;
                end if;
            end if;
        end loop;
        return false;
    end has_common_words;

    procedure save_decrypted
    (
        i_chapter_no giw_codes.chapter_no%type,
        i_encryption_method giw_codes.encryption_method%type,
        i_message giw_codes.message%type
    )
    is
    --
    -- Update the GIW_CODES record identified by i_book_edition and i_chapter_no
    -- with i_encryption_method and i_message
    --
    begin
        update giw_codes
        set encryption_method = i_encryption_method,
            message = i_message
        where chapter_no = i_chapter_no;
    end save_decrypted;

    function caesar( i_cryptogram in giw_codes.cryptogram%type, i_offset in pls_integer)
        return giw_codes.message%type
    is
        --
        -- Translate i_cryptogram using an offset of i_offset this_chars.
        -- This is essentially brutus.sql made a bit more respectable now it's part of a package...
        --
        phrase varchar2(4000); 
        offset pls_integer;
        this_char varchar2(1);
        decrypted_char varchar2(1);
        decrypted_phrase varchar2(32767);
        ascii_a pls_integer;
    begin
        -- Parameter sanity check...
        if i_cryptogram is null or i_offset is null then
            raise_application_error(-20000, 'Both the cryptogram and the number of this_chars to offset must be supplied');
        end if;

        for i in 1..length( i_cryptogram) loop
            this_char := substr( i_cryptogram, i, 1);
            if ascii( upper( this_char)) not between UPPER_A and UPPER_Z then
                -- not a letter, just use the this_char unchanged
                decrypted_char :=  this_char;
            else
                -- Make sure that the this_char stays within the bounds of
                -- alpha ascii codes after the offset is applied
                ascii_a := case when ascii(this_char) between UPPER_A and UPPER_Z then UPPER_A else LOWER_A end;

                -- now apply the offset...
                decrypted_char := chr( ascii( this_char) - i_offset);

                if ascii(decrypted_char) < ascii_a then
                    -- this_char needs to "wrap around" to the other end of the alphabet
                    decrypted_char := chr( ascii(decrypted_char) + 26);
                end if;
            end if;
            decrypted_phrase := decrypted_phrase||decrypted_char;
        end loop;
        return decrypted_phrase;
    end caesar;
    
    procedure brute_force_caesar is
    --
    -- Cycle through all of the undeciphered cryptograms in GIW_CODES.
    -- Check the "decrypted" string for common words and if it passes this test, update the record in GIW_CODES
    --
    
        candidate_string giw_codes.message%type;
        
    begin
        for r_phrase in
        (
            select chapter_no, cryptogram
            from giw_codes
            where encryption_method is null
            order by 1,2
        )
        loop
            for i in 1..25 loop
                -- Loop through each possible Caesar cipher, stop if we get a match
                candidate_string := caesar( r_phrase.cryptogram, i);
                if has_common_words( candidate_string) then
                    save_decrypted( r_phrase.chapter_no, 'CAESAR', candidate_string);
                    exit;
                end if;
            end loop;
        end loop;
    end brute_force_caesar;
end decrypt;
/

After all of that, let’s see how many cryptograms we can decipher…

begin
    decrypt.brute_force_caesar;
end;
/

PL/SQL procedure successfully completed.

commit;

…and through the medium of sqlcl, we can see…

crack_caesar

So, it looks like 14 of our cryptograms are using a Caesar Cipher. Now for the the other 22…

Chipping away with Vigenere

It’s rather appropriate in an era of post-truth that we now come to a Cipher that’s not named after the person who invented it.
The Vigenere Cipher works in the same way as the Caesar Cipher but adds a key phrase into the mix. The key dictates the offset for each individual letter in the message.
It’s possible to lookup the letter code for each letter of the key using a grid known as a Vigenere Square or Vigenere Table.
Hmmm, table, that gives me an idea…

create table vigenere_squares
(
    cipher_key varchar2(1),
    plain_value varchar2(1),
    cipher_value varchar2(1)
)
/

comment on table vigenere_squares is
    'Table to translate the Vigenere Cipher. For a given key character (cipher_key), the cipher_value translates to the plain_value'
/

declare
    cipher varchar2(1);
    key varchar2(1);
    plain varchar2(1);
begin
    for i in 65..90 loop
        key := chr(i);
        for j in 0..25 loop
            plain := chr( j + 65);
            if (ascii(key) + j) > 90 then
                cipher := chr(ascii( key) + j - 26);
            else
                cipher := chr( ascii( key) + j);
            end if;
            insert into vigenere_squares( cipher_key, plain_value, cipher_value)
            values( key, plain, cipher);
        end loop;
    end loop;
end;
/

So, if our key phrase contains, say F ( for Fabien), we can lookup the translation for any letter…

vigenere_f

Alexandre Dumas is reputed to have remarked that English is French, badly spoken. As a true Englishman, with requisite apalling French accent, when someone mentions Vigenere, I automatically think of vinegar…malt vinegar – the condiment that defines the Great British Chip and distinguishes it from those continental frites which are usually subjected to mayonnaise.
Let’s see if we can ensure that the cipher used in chapter 2 has had it’s chips( or French Fries if your American).
Once again, let’s pretend I haven’t just copied Fabien but have instead been struck by an inspiration and have tried to use the answer to the question in chapter one as the key for the cipher in chapter2…

set serveroutput on size unlimited
declare
    phrase varchar2(4000);
    key varchar2(100);
    ptr pls_integer := 1;
    enc_char varchar2(1);
    plain_char varchar2(1);
    message varchar2(4000);
    
begin
    phrase := 'wbth lal voe htat oy voe wxbirtn vfzbqt wagye C poh aeovsn vojgav ?';
    key := 'AUTOPATCH';

    for i in 1..length(phrase) loop
        enc_char := substr( phrase, i, 1);
        if ascii( upper( enc_char)) not between 65 and 90 then
            -- not an alpha character...
            plain_char := enc_char;
        else
            -- lookup the plain value, preserving case
            select case when ascii( enc_char) between 65 and 90 then plain_value else lower(plain_value) end
            into plain_char
            from vigenere_squares
            where cipher_value = upper( enc_char)
            and cipher_key = upper( substr( key, ptr, 1));

            -- Move to the next character in the key phrase 
            ptr := ptr + 1;
            if ptr > length( key) then
                -- we've reached the end of the key, loop around to the start
                ptr := 1;
            end if;
        end if;

        message := message||plain_char;
    end loop;
    dbms_output.put_line(message);
end;
/

…sure enough…

what was the name of the central office where I was almost caught ?

The next step then, would be to fill in the answers to all of the questions we’ve already decoded in case the answer for the Caesar Ciphered question in one chapter always provides the key for the Vigenere Ciphered question in the next…*sound of pages being turned*

update giw_codes set answer = 'AUTOPATCH' where chapter_no = 1;
update giw_codes set answer = 'JELLY' where chapter_no = 3;
update giw_codes set answer = 'OROVILLE' where chapter_no = 5;
update giw_codes set answer = 'BILLCOOK' where chapter_no = 7;
update giw_codes set answer = 'FIRMWARE' where chapter_no = 9;
update giw_codes set answer = 'CALABASAS' where chapter_no = 11;
update giw_codes set answer = 'TELTEC' where chapter_no = 13;
update giw_codes set answer = 'OPTOELECTRONICS' where chapter_no = 15;
update giw_codes set answer = 'OAKWOOD' where chapter_no = 17;
update giw_codes set answer = 'ALPHADENT' where chapter_no = 19;
update giw_codes set answer = 'BOOMBOX' where chapter_no = 23;
update giw_codes set answer = 'ELLENSBURG' where chapter_no = 25;
update giw_codes set answer = 'GTETELENET' where chapter_no = 31;
update giw_codes set answer = 'ROBERTMORRIS' where chapter_no = 33;

commit;

We can add vigenere decryption functionality into the package by means of a new couple of new procedures. In the package header, we add :

-- Decrypt i_cryptogram using vigenere cipher with i_key as key
function vigenere( i_cryptogram in giw_codes.cryptogram%type, i_key in varchar2)
    return giw_codes.message%type;

-- If we have enough data, check to see if any un-cracked cryptograms could be Vigenere Ciphered
procedure chip_away;

The first is to apply the Vigenere decryption itself…

...
    function vigenere( i_cryptogram in giw_codes.cryptogram%type, i_key in varchar2)
        return giw_codes.message%type
    is 
    --
    -- Decrypt i_cryptogram using the letters in phrase i_key to determine the offset for each character.
    --
    ptr pls_integer := 1;
    enc_char varchar2(1);
    plain_char varchar2(1);
    message giw_codes.message%type;
    begin
        -- parameter sanity check...
        if i_cryptogram is null or i_key is null then
            -- Same error as for Caesar above but use a different error number to distinguish it
            raise_application_error(-20001, 'Both the cryptogram and the number of characters to offset must be supplied');
        end if;
        for i in 1..length(i_cryptogram) loop
            enc_char := substr( i_cryptogram, i, 1);
            if ascii( upper( enc_char)) not between UPPER_A and UPPER_Z then
                -- not an alpha character...
                plain_char := enc_char;
            else

                select case when ascii( enc_char) between UPPER_A and UPPER_Z then plain_value else lower(plain_value) end
                into plain_char
                from vigenere_squares
                where cipher_value = upper( enc_char)
                and cipher_key = upper( substr( i_key, ptr, 1));

                -- Move the pointer to the next character in the key
                ptr := ptr + 1;
                if ptr > length( i_key) then
                    ptr := 1;
                end if;
            end if;

            message := message||plain_char;
        end loop;
        return message;
    end vigenere;
...

… and the second is to loop through the GIW_CODES table and see what records we can apply this to …

...
    procedure chip_away is
    --
    -- Attempt to decrypt any cryptograms that have not yet been cracked where we
    -- have an answer to the previous chapter's message to use as a key.
    --
        candidate_string giw_codes.message%type;
        key_string giw_codes.answer%type;
    begin
        for r_phrase in
        (
            select chapter_no, cryptogram, encryption_method, answer
            from giw_codes
            order by 1,2
        )
        loop
            -- Go through each record in the table...
            if r_phrase.encryption_method is not null
                and r_phrase.answer is not null
            then
                -- although this cryptogram has already been solved, the answer may serve as the
                -- key for the next record if it has been encrypted with a Vigenere Cipher...
                key_string := r_phrase.answer;
                continue;
             elsif r_phrase.encryption_method is null
                and key_string is not null
             then
                candidate_string := vigenere( r_phrase.cryptogram, key_string);
                if has_common_words( candidate_string) then
                    save_decrypted(r_phrase.chapter_no, 'VIGENERE', candidate_string);
                end if;
            end if;
        end loop;
    end chip_away;
...

…we can use it to plug some more of the gaps we have…

In the best British tradition, if it's not working, blame the French

In the best British tradition, if it’s not working, blame the French

Whilst some of the remaining uncracked cryptograms look suspiciously like they could be encrypted with Vigenere, others look rather different.
Our next challenge is rather more to do with numbers than letters (at least initially)…

Hello Hexy

Taking a look at the cryptogram for chapter 21, it does appear to be comprised of a series of hexadecimal numbers. What was that ? Fabien who ?…

select cryptogram
from giw_codes
where chapter_no = 21
/

CRYPTOGRAM                                                                                                                         

4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F   

Now PL/SQL has whizzy built-in function to make conversion from hex to to varchar2 that little bit easier…

set serveroutput on size unlimited
declare
    SPACE constant varchar2(1) := chr(32);
    phrase varchar2(4000);
    ptr pls_integer := 1;
    hex_num varchar2(3);
    dec_phrase varchar2(4000);

    candidate_string varchar2(4000);
begin
    phrase := '4A 75 6E 67 20 6A 6E 66 20 62 68 65 20 61 76 70 78 61 6E 7A 72 20 74 76 69 72 61 20 67 62 20 47 72 65 65 6C 20 55 6E 65 71 6C 3F ';
    while ptr < length( phrase) loop
        -- take each hex number in the string...
        hex_num := substr( phrase, ptr, instr( phrase, SPACE, ptr, 1)- ptr);
        -- ...and use a standard package and function together to convert to decimal
        dec_phrase := dec_phrase|| utl_raw.cast_to_varchar2( hextoraw( hex_num));
        ptr := instr( phrase, SPACE, ptr, 1) + 1;
    end loop;
    dbms_output.put_line( dec_phrase);
    -- Now see if we can decrypt the resulting string...
    for i in 1..25 loop
        candidate_string := decrypt.caesar( dec_phrase, i);
        if decrypt.has_common_words( candidate_string) then
            dbms_output.put_line('Saving : '||candidate_string);
            decrypt.save_decrypted( 21, 'HEX-CAESAR', candidate_string);
            exit;
        end if;
    end loop;
end;
/

…which looks rather promising…

@hexy_beast
Jung jnf bhe avpxanzr tvira gb Greel Uneql?
Saving : What was our nickname given to Terry Hardy?


PL/SQL procedure successfully completed.

commit;

Commit complete.
 

As we can now provide an answer to this question, we can potentially re-visit the cryptogram for chapter 22 which may well be a Vigenere string…

select cryptogram
from giw_codes
where chapter_no = 22
/

CRYPTOGRAM                                                              
----------------------------------------------------------------------
Gsig cof dsm fkqeoe vnss jo farj tbb epr Csyvd Nnxub mzlr ut grp lne ?  

First of all, we need to update the chapter 21 record with the answer…

update giw_codes set answer = 'KLINGON' where chapter_no = 21;
commit;

As I’m feeling lazy, I’ll just re-run the chip_away procedure…

ch22

If we look at what remains unsolved, most of them appear to follow the pattern of some numeric code followed by what may well be a vigenere enciphered string.
There is, however, one obvious exception to this…

Base64 Encoding

Looking at Fabien’s observations the cryptogram for chapter 37, the “==” at the end of the string seems typical of the padding for a Base64 encoded string :

select cryptogram
from giw_codes
where chapter_no = 37
/
CRYPTOGRAM                                                                                                
-----------------------------------------------------------------------
V2hhdCBGQkkgYWdlbnQgYXNrZWQgU3VuIE1pY3Jvc3lzdGVtcyB0byBjbGFpbSB0aGV5IGxvc3QgODAgbWlsbGlvbiBkb2xsYXJzPw==  

Once again, PL/SQL makes decrypting this somewhat simpler than you migh think…

select utl_raw.cast_to_varchar2( utl_encode.base64_decode( utl_raw.cast_to_raw( cryptogram)))
from giw_codes
where chapter_no = 37
/

UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(CRYPTOGRAM)))  
--------------------------------------------------------------------------------
What FBI agent asked Sun Microsystems to claim they lost 80 million dollars?   

That makes things simple then….

update giw_codes
set message = utl_raw.cast_to_varchar2( utl_encode.base64_decode( utl_raw.cast_to_raw( cryptogram))),
    encryption_method = 'BASE64'
where chapter_no = 37
/
commit;

Right, now to turn our attention back to those “numeric” cryptograms…

Converting other bases to Decimal

If we examine the cryptograms for chapters 27…

85 102 121 114 32 103 113 32 114 102 99 32 108 121 107 99 32 109 100 32 114 102 99 32 122 109 105 113 114 109 112 99 32 71 32 100 112 99 111 115 99 108 114 99 98 32 103 108 32 66 99 108 116 99 112 63 

…29…

126 147 172 163  040  166 172 162  040  154 170  040  157 172 162 162 166 156 161 143  040  145 156 161 040  163 147 144  040  115 156 165 144 153 153  040  163 144 161 154 150 155 172 153  040  162 144 161 165 144 161  040  150 155  040  122 172 155 040 111 156 162 144 077 

…and 35…

2B 2T W 2X 2Z 36 36 2P 36 2V 3C W 3A 32 39 38 2Z W 3D 33 31 38 2V 36 3D W 2R 2Z 3C 2Z W 3E 3C 2V 2X 2Z 2Y W 3E 39 W 2R 32 2V 3E W 2V 3A 2V 3C 3E 37 2Z 38 3E W 2X 39 37 3A 36 2Z 2S 1R

…they all look like numeric representations of ascii character values in a variety of bases.

Whilst we could write a program for each of these cryptograms, it’s so much less effort to do something that covers all three.
It also gives me the opportunity to use another package name which may not be entirely consistent with a real-world naming convention…

create or replace package under_crackers
as
--
-- Translate numeric cryptograms to characters
--
    -- Package constants, some of which may look familiar...

    ASCII_0 constant pls_integer := ascii('0');
    ASCII_9 constant pls_integer := ascii('9');
    SPACE constant varchar2(1) := chr(32); -- a space

    -- take i_number in base i_base and return the decimal equivalent.
    -- NOTE that i_number is a character string because bases above 10 include non-numeric characters.
    function base_to_decimal( i_number in varchar2, i_base in pls_integer)
        return pls_integer;

    -- return the character string represented by the ascii codes in i_cryptogram, which is currently in i_base.
    function code_to_string( i_cryptogram in giw_codes.cryptogram%type, i_base in pls_integer)
        return varchar2;
end under_crackers;
/

create or replace package body under_crackers
as
    function base_to_decimal( i_number in varchar2, i_base in pls_integer)
        return pls_integer
    is
        revnum varchar2(38);
        rtnval pls_integer := 0;
        digit varchar2(1);

        e_invalid_digit_for_base exception;
    begin
        --
        -- Sanity checks
        --
        if i_number is null or i_base is null then
            raise_application_error( -20000, 'Both a number and a base must be specified');
        elsif i_base not between 2 and 36 then
            raise_application_error( -20001, 'This function only converts bases 2 - 36');
        elsif i_base > 10 then
            -- make sure this is a valid i_base number
            if instr( i_number, chr(55 + i_base),1,1) > 0 then
                raise e_invalid_digit_for_base;
            end if;
        elsif i_base < 10 then
            if instr( i_number, i_base, 1, 1) > 0 then
                raise e_invalid_digit_for_base;
            end if;
        end if;
        -- Reverse the "digits" in i_number. That way we can loop through and add the decimal numbers represented by the
        -- characters in i_number without having to check how long it is first.
        -- the REVERSE function is a SQL, rather than PL/SQL built-in, hence...
        select reverse(i_number) into revnum from dual;
        for i in 1..length(revnum) loop
            digit := substr(revnum, i, 1);
            if ascii(digit) between ASCII_0 and ASCII_9 then
                rtnval := rtnval + ( digit * power(i_base, i - 1));
            else
                -- letters in bases above 10 are always offset from 10 - e.g. A = 10, B = 11 etc.
                -- so, subtracting 55 from the ascii code of the upper case letter will give us the decimal value
                rtnval := rtnval + ( ( ascii( upper( digit)) - 55) * power( i_base, i - 1) );
            end if;
         end loop;
         return rtnval;
    exception when e_invalid_digit_for_base then
        raise_application_error( -20002, 'Number '||i_number||' is not a valid '||i_base||' number.');
    end base_to_decimal;

    function code_to_string( i_cryptogram in giw_codes.cryptogram%type, i_base in pls_integer)
        return varchar2
    is
        ptr pls_integer := 1;
        this_num varchar2(38);
        decval pls_integer;
        rtn_string varchar2(4000);
    begin
        -- loop through each of the numbers in i_cryptogram and convert them to decimal...
        while ptr < length( i_cryptogram)
        loop
            -- add a trailing space to the string so we can easily move the pointer to the end of it
            this_num := substr( i_cryptogram||SPACE, ptr, instr( i_cryptogram||SPACE, SPACE, ptr, 1) - ptr);
            if i_base != 10 then
                decval := base_to_decimal(this_num, i_base);
            else
                decval := this_num;
            end if;
            -- convert the number ( ascii code) to the character it represents and append it to the output string
            rtn_string := rtn_string||chr(decval);
            -- increment the pointer to the next number in the string
            ptr := instr( i_cryptogram||SPACE, SPACE, ptr, 1) + 1;
        end loop;
        return rtn_string;
    end code_to_string;
end under_crackers;
/

Now for a test. the fact that “040” crops up quite a lot in the chapter 29 cryptogram may suggest that it is an octal representation of the ASCII code for a space ( decimal value of 32)…

select under_crackers.code_to_string(cryptogram, 8)
from giw_codes
where chapter_no = 29
/

UNDER_CRACKERS.CODE_TO_STRINGCRYPTOGRAM,8)
----------------------------------------------------------------
Vgzs vzr lx ozrrvnqc enq sgd Mnudkk sdqlhmzk rdqudq hm Rzm Inrd?

The acid test of all this is whether we can now decipher is using Caesar…

set serveroutput on size unlimited
declare
    message giw_codes.message%type;
begin
    for i in 1..25 loop
        message := decrypt.caesar('Vgzs vzr lx ozrrvnqc enq sgd Mnudkk sdqlhmzk rdqudq hm Rzm Inrd?', i);
        if decrypt.has_common_words( message) then
            dbms_output.put_line(message);
            exit;
        end if;
    end loop;
end;
/
What was my password for the Novell terminal server in San Jose?


PL/SQL procedure successfully completed.

That looks promising. Using the same “spot the space character” approach ( and checking back on Fabien’s work), we can hypothesise that chapter 27 is in straight decimal whilst chapter 35 is in base 36.
So, we can now run this :

set serveroutput on size unlimited
declare
    candidate_string giw_codes.message%type;
    encryption_method giw_codes.encryption_method%type;
begin
    for r_phrase in
    (
        select chapter_no,
            under_crackers.code_to_string
            (
                cryptogram,
                case chapter_no
                    when 27 then 10
                    when 29 then 8
                    when 35 then 36
                end
            ) as message
        from giw_codes
        where chapter_no in (27,29,35)
        order by chapter_no
    )
    loop
        encryption_method :=
            'BASE'||case r_phrase.chapter_no when 27 then 10 when 29 then 8 when 35 then 36 end||'-CAESAR';
        for i in 1..25 loop
            candidate_string := decrypt.caesar( r_phrase.message, i);
            if decrypt.has_common_words( candidate_string) then
                dbms_output.put_line('Chapter : '||r_phrase.chapter_no);
                dbms_output.put_line('Message : '||candidate_string);
                decrypt.save_decrypted( r_phrase.chapter_no, encryption_method, candidate_string);
                exit;
             end if;
         end loop;
    end loop;
    dbms_output.put_line('Review updated records and commit if OK. Rollback if not.');
end;
/

…and we’re rewareded with…

Chapter : 27
Message : What is the name of the bokstore I frequented in Denver?
Chapter : 29
Message : What was my password for the Novell terminal server in San Jose?
Chapter : 35
Message : My cellular phone signals were traced to what apartment complex?
Review updated records and commit if OK. Rollback if not.

We can now commit these updates.

Better still, providing some more answers….

update giw_codes set answer = 'TATTEREDCOVER' where chapter_no = 27;
update giw_codes set answer = 'SNOWBIRD' where chapter_no = 29;
update giw_codes set answer = 'PLAYERSCLUB' where chapter_no = 35;

commit;

…allows us to solve the outstanding vigenere ciphers…

base_answers

You’re probably wondering why I haven’t included the last cipher in this base conversion exercise. After all, it has to be binary, right ?

100-1111-10-0 011-000-1-111 00-0100 1101-10-1110-000-101-11-0-1 0111-110-00-1001-1-101 111-0-11-0101-010-1-101 111-10-0100 110011

Well, apparently, it isn’t…

One morse time

Yes, it is indeed, a representation of morse code.
The ‘1’s are the ‘.’s and the ‘0’s are the ‘-‘.

Fortunately, it’s easy enough to teach PL/SQL a bit of morse code…

create table morse_codes
(
    letter varchar2(1),
    morse varchar2(10)
)
/

--
-- Codes as per - https://en.wikipedia.org/wiki/Morse_code#Symbol_representations
--
insert into morse_codes( letter, morse) values ('A', '.-');
insert into morse_codes( letter, morse) values ('B', '-...');
insert into morse_codes( letter, morse) values ('C', '-.-.');
insert into morse_codes( letter, morse) values ('D', '-..');
insert into morse_codes( letter, morse) values ('E', '.');
insert into morse_codes( letter, morse) values ('F', '..-.');
insert into morse_codes( letter, morse) values ('G', '--.');
insert into morse_codes( letter, morse) values ('H', '....');
insert into morse_codes( letter, morse) values ('I', '..');
insert into morse_codes( letter, morse) values ('J', '.---');
insert into morse_codes( letter, morse) values ('K', '-.-');
insert into morse_codes( letter, morse) values ('L', '.-..');
insert into morse_codes( letter, morse) values ('M', '--');
insert into morse_codes( letter, morse) values ('N', '-.');
insert into morse_codes( letter, morse) values ('O', '---');
insert into morse_codes( letter, morse) values ('P', '.--.');
insert into morse_codes( letter, morse) values ('Q', '--.-');
insert into morse_codes( letter, morse) values ('R', '.-.');
insert into morse_codes( letter, morse) values ('S', '...');
insert into morse_codes( letter, morse) values ('T', '-');
insert into morse_codes( letter, morse) values ('U', '..-');
insert into morse_codes( letter, morse) values ('V', '...-');
insert into morse_codes( letter, morse) values ('W', '.--');
insert into morse_codes( letter, morse) values ('X', '-..-');
insert into morse_codes( letter, morse) values ('Y', '-.--');
insert into morse_codes( letter, morse) values ('Z', '--..');
--
-- Numbers
--
insert into morse_codes( letter, morse) values ('1', '.----');
insert into morse_codes( letter, morse) values ('2', '..---');
insert into morse_codes( letter, morse) values ('3', '...--');
insert into morse_codes( letter, morse) values ('4', '....-');
insert into morse_codes( letter, morse) values ('5', '.....');
insert into morse_codes( letter, morse) values ('6', '-....');
insert into morse_codes( letter, morse) values ('7', '--...');
insert into morse_codes( letter, morse) values ('8', '---..');
insert into morse_codes( letter, morse) values ('9', '----.');
insert into morse_codes( letter, morse) values ('0', '-----');

--
-- Punctuation
--
insert into morse_codes( letter, morse) values ('.', '.-.-.-');
insert into morse_codes( letter, morse) values (',', '--..--');
insert into morse_codes( letter, morse) values ('?', '..--..');
insert into morse_codes( letter, morse) values (q'[']', '.----.');
insert into morse_codes( letter, morse) values ('!', '-.-.--');
insert into morse_codes( letter, morse) values ('/', '-..-.');
insert into morse_codes( letter, morse) values ('(', '-.--.');
insert into morse_codes( letter, morse) values (')', '-.--.-');
insert into morse_codes( letter, morse) values ('&', '.-...');
insert into morse_codes( letter, morse) values (':', '---...');
insert into morse_codes( letter, morse) values (';', '-.-.-.');
insert into morse_codes( letter, morse) values ('=', '-...-');
insert into morse_codes( letter, morse) values ('+', '.-.-.');
insert into morse_codes( letter, morse) values ('-', '-....-');
insert into morse_codes( letter, morse) values ('_', '..--.-');
insert into morse_codes( letter, morse) values ('"', '.-..-.');
insert into morse_codes( letter, morse) values ('$', '...-..-');
insert into morse_codes( letter, morse) values ('@', '.--.-.');

commit;

The structure of the cryptogram itself is slightly different from the “base” strings in that it contains both character separators (“-“) and word separators ( space).
We can utilise this characteristic to split the string into words and then translate each word a character at a time.
As for converting “.-” to “10”, that’s rather neatly handled by the SQL TRANSLATE function…

set serveroutput on size unlimited
declare
    SPACE constant varchar2(1) := chr(32);
    delimiter varchar2(1) := chr(45); -- '-'
    phrase varchar2(32767);
    word varchar2(100);
    ptr pls_integer := 1;
    wptr pls_integer;
    this_code varchar2(100);
    this_char varchar2(1);
    candidate_string giw_codes.message%type;
    
begin
    -- Append a "word" delimiter to the end of the phrase to make splitting it a bit easier
    select cryptogram||SPACE
    into phrase
    from giw_codes
    where chapter_no = 38;

    for i in 1..regexp_count(phrase, SPACE) loop
        -- Get each complete word in the phrase...
        word := substr( phrase, ptr, instr( phrase, SPACE, ptr, 1) - ptr)||delimiter;
        wptr := 1;
        for j in 1..regexp_count(word, delimiter) loop
            -- ...and do a character-by-character translation
            this_code := substr( word, wptr, instr( word, delimiter, wptr, 1) - wptr);

            select letter into this_char
            from morse_codes
            where translate(morse, '.-', '10') = this_code;

            candidate_string := candidate_string||this_char;
            wptr := instr( word, delimiter, wptr, 1) + 1;
        end loop;
        -- Maintain the word boundaries
        candidate_string := candidate_string||SPACE;
        ptr := instr(phrase, SPACE, ptr, 1) + 1;
    end loop;
    dbms_output.put_line(candidate_string);
    decrypt.save_decrypted( 38, 'MORSE', candidate_string);
end;
/

Run this and we get…

WHAT DOES MY FAVORITE BUMPER STICKER SAY ? 

After all of that, we can now review the results of all that deciphering.

If we now run …

select chapter_no, encryption_method, message
from giw_codes
order by chapter_no
/

…we get…

all_answers

In keeping with the theme of these post, I’ll conclude by saying :

Ar tzs Cgyk Xvnbu Gyw TL/KEY kzzpp kwpqk !

…and leave you with Seasons Greetings.


Filed under: Oracle, PL/SQL Tagged: ASCII function, Caesar Cipher, CHR, hextoraw, index organized table, morse code, regexp_count, reverse, utl_encode.base64_decode, utl_raw.cast_to_raw, utl_raw.cast_to_varchar2, Vigenere Cipher

Pages