The Anti-Kyte

Subscribe to The Anti-Kyte feed The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 6 months 3 weeks ago

First Steps in SQLDeveloper Data Modeler

Wed, 2018-05-30 13:12

It’s true, Oracle are giving away free stuff. “Oracle ?”, I hear you say, “as in Larry’s Database Emporium and Cloud base ?” The very same.
It’s been going on for quite a while and includes relatively hidden gems such as SQLDeveloper Data Modeler.

There is some confusion around this particular tool for a couple of reasons.
When it was first released (sometime around 2009 as I recall), Data Modeler was an additional cost option. However, that didn’t last long.
At present (and for a number of years now), it is available either as a completely standalone tool, or as a fully integrated component of the SQLDeveloper IDE.
Either way, it costs exactly the same as the SQLDeveloper IDE – i.e. nothing.

I can tell you like the price, want to take it for a spin ?

I’m going to focus here on using the integrated version of Data Modeler. This is because

  • I want to use it for small-scale modelling of the type you might expect to find when using an Agile Methodology
  • I’m a developer and don’t want to leave the comfort of my IDE if I don’t need to

What I’m going to cover is :

  • Viewing a Table Relationship Diagram (TRD) for an existing database table
  • Creating a Logical Data Model and Entity Relationship Diagram (ERD)
  • Generating a physical model from a logical model
  • Generating DDL from a Physical Model (including some scripting tweaks to suit your needs)
  • Using a Reporting Schema and pre-canned SQLDeveloper Reports to explore your models

Disclaimer
This post is about introducing the features of Data Modeler in the hope that you may find them useful.
It’s not intended as a paragon of data modelling virtue.
Come to that, it’s not intended as a definitive guide on how to use this tool. I’m no expert with Data Modeler (as you are about to find out). Fortunately, there are people out there who are.
If, after reading this, you want to explore further, then you could do worse than checking out words of Data Modeler wisdom from :

Let’s get started…

The Model Tab

Say I’m connected to the database as HR and I’m looking at the DEPARTMENTS table.
I’d really like to see a TRD for this table.
The bad news is that the data model originally created when building the application fell into disuse long ago and is now hopelessly outdated.
The good news is that I’m using a recent version of SQLDeveloper ( 18.1, since you ask), so I just need to navigate to the DEPARTMENTS table in the tree, hit the Model Tab and wait a few seconds…

That’s Data Modeler doing it’s thing – in this case, reading information from the Data Dictionary and building the resulting diagram on the fly.
Note that, you may wait some time for the tab to initialize and then be greeted with an apparently empty space. I’ve found that this is especially true when looking at Fact tables in a star-schema.
The tables are there, just not where you can see them.
If you want to explore the diagram, tweak the layout, or even just export it to a file, you can click on the Copy to Data Modeler button :

Incidentally, if you do have a diagram where nothing is visible, you should now be able to use the Fit Screen button to make sure you can see everything.

You should then be able to drag objects around and amend the layout to suit your purposes.
Once you’re finished, if you’d like to save the diagram into a separate file, you can use the right-click menu and select Print Diagram :

Whilst it’s extremely useful to be able to generate an accurate and up-to-date TRD from the Data Dictionary, where Data Modeler comes into it’s own is when you want to …er…create a Data Model.

Creating a Data Model for a new Application

The traditional approach to Data Modelling was to create a full sized, complete model before moving on to write the rest of the application. The extended timelines for this approach may not be practical if you are following one of the Agile development methodologies. However, having a properly designed data model is rather important for an Application running on the Oracle platform.
We can go some way to squaring this circle by creating models for sub-sections of the application in one sprint for the developers to code against in the next sprint.
This is the context in which I imagine the modelling that follows to be taking place.

In this case, I’m using my trusty World Cup Wallchart application as the example. This is a “new” application rather than being an enhancement to an existing one.
The Entities I’ve identified at this point are :

  • A COMPETITION is an international football competition, such as the World Cup or the Copa America
  • A TOURNAMENT is an instance of a COMPETITION (e.g. Russia 2018 is a World Cup Finals Tournament).
  • A TEAM is a competitior in international football and may compete in one or more TOURNAMENTs, such as Brazil in all World Cups or England (briefly) in some

Right, let’s get cracking then, starting with…

The Logical Model

The first challenge is to choose the correct Data Modeler sub-menu to open to start creating our model. There are three separate entries in the IDE – in the File menu, the View menu, and the Tools menu.

In this case, we want to open the Data Modeler Browser tree using View/Data Modeler/Browser

…which opens the browser in the left-hand pane.

Note – You can find Jeff Smith’s useful guide to the Integrated Data Modeler menus here.

If we expand the Untitled_1 node we can see some items which may be of interest…

We can now right-click the Logical Model node and select Show from the pop-up menu and we can see that a Diagram area, together with a widget toolbar appears…

We need to use the toolbar widgets to create our model objects.
The toolbar looks like this :

The widgets are :

  • Select
  • New Entity
  • New View
  • New M:N Relation
  • New 1:N Relation
  • New 1:N Relation Identifying
  • New Type Substitution
  • New Arc
  • Add Foreign Key to Arc
  • Remove Foreign Key from Arc
  • New Note
  • New Picture
  • Delete
  • Engineer to Relational Model
  • Zoom in
  • Zoom Out
  • Fit Screen
  • Default Size
  • Search

For now, we’re going to click on New Entity and then click in the diagram area, which presents us with :

Our first Entity is COMPETITION. We specify this in the Name field in the General Tab.
Next we select Attributes section of the Entity Properties so that we can start specifying the COMPETITION attributes.

To add an attribute, simply click the green plus button in the Attributes toolbar that appears and then fill in the details.
In this case, I’m adding a column called COMP_CID which is a VARCHAR with a maximum length of 25 characters.
The value in this column will uniquely identify an instance of a COMPETITION so I’ve ticked the Primary UID box.
I’ve also selected the Comments in RDBMS Tab and added a comment for what will ultimately become a column in the finished database table.
Finally, I’ve hit the Apply button and can now see this :

If we now navigate to the Unique Identifiers section we can see that a Primary Key has been created for us

Once we’ve returned to Attributes and added a few more, the finishing touch for this entity is to navigate to the Comments in RDBMS section.
This time, we’re entering comments for what will ultimately be the database table :

Once we’re finished, we can now see our finished entity in the diagram :

Having added the other Entities, our model now looks like this :

We can now save the design by going to the File menu and clicking Data Modeler/Save.
We need to save the design as a file with a .dmd extension.
Notice that once you’ve saved the file, the name of the Untitled_1 node in the Data Modeler browser changes to the base name of the file you’ve just created.
Notice also that at no point have we been connected to the database during the creation of our model.
In fact, it’s quite possible to progress from a Logical Model to a Physical model and even generate the DDL required to implement it without connecting to a database at any point.
However, SQLDeveloper does offer some useful Data Modeler reports which we can take advantage of if we decide to create a Reporting Schema.

Using a Reporting Schema

To start with, we need to create the Reporting Schema, in this case, DM_REPORT_REPOS :

set verify off
accept passwd prompt 'Password for DM_REPORT_REPOS : ' hide
create user dm_report_repos identified by &passwd
/

alter user dm_report_repos default tablespace users
/

alter user dm_report_repos quota unlimited on users
/

Now we need to edit and run the Reporting_Schema_Permissions.sql script, which is located in the datamodeler\datamodeler\reports folder.

In my case, runninig on an Ubuntu client with SQLDeveloper installed in /opt/sqldeveloper181, the path to this script is :

/opt/sqldeveloper181/sqldeveloper/sqldeveloper/extensions/oracle.datamodeler/reports/Reporting_Schema_Permissions.sql

Before running the script, we need to edit the file to replace and with desired values:

  • is the schema to hold the reporting repository (DM_REPORT_REPOS in this example)
  • is a directory on the database server

Incidentally, I’ve also created a new directory on the Operating System so that when the script creates the directory object in the database, it will be pointing to an existing directory on the OS :

sudo su oracle
mkdir /u01/app/oracle/dm_reports

With my changes, the script now looks like this :

CREATE OR REPLACE DIRECTORY OSDDM_REPORTS_DIR AS '/u01/app/oracle/dm_reports';
GRANT READ, WRITE ON DIRECTORY OSDDM_REPORTS_DIR TO dm_report_repos;
GRANT CREATE SESSION TO dm_report_repos;
GRANT RESOURCE TO dm_report_repos;
GRANT CREATE TABLE TO dm_report_repos;
GRANT CREATE SEQUENCE TO dm_report_repos;
GRANT CREATE VIEW TO dm_report_repos;
GRANT CREATE PROCEDURE TO dm_report_repos;

Once we’ve created the Repository schema and added a connection for it in SQLDeveloper, we can then report on our model.
In order to do this, we first need to export our model into the repository.

So, go to the File Menu and select Data Modeler/Export/To Reporting Schema.

You should be rewarded with :

Select the DM_REPORT_REPOS connection and hit OK. You should eventually get :

If the Reports Tree is not already visible, open it by selecting on View/Reports.

If you now expand the Data Modeler Reports node, you’ll see a number of pre-built reports available.
For example, I can see which of my Logical entities are missing relationships :

It’s probably worth remembering that, after you’ve made changes to your data model, you will need to export it again to the reporting repository for those changes to be reflected in the reports.

Anyway, it’s clear that we need to finish off our Logical model with some relations.

First, we create a 1:N relationship between COMPETITION and TOURNAMENT by clicking on the 1:N widget then clicking in the COMPETITION entity and then in the TOURNAMENT ENTITY.
Once the line appears on the diagram we can then name the relationship :

If we now go into the TOURNAMENTS entity properties, we can see that the COMP_CID column has been added automatically :

We can now also add it to the TOURNAMENTS existing Unique Key :

One final point to note is that, although it’s not yet apparent, we are going to have a Surrogate Key for TOURNAMENT :

Finally, we’re going to add a Many-to-Many ( M:N relationship) between TEAM and TOURNAMENT, which leaves our logical model looking like this :

Let’s see how smart Data Modeler is when we move on to…

Engineering a Relational Model from the Logical Model

With the Logical Model Diagram displayed, if we hit the Engineer to Relational Model button in the toolbar we’ll get this window :

Now hit the Engineer button and you should now see :

The main thing that jumps out when looking at this are :

  • the layout could do with some work
  • Tournament has had TOURNAMENT_ID column generated which is now it’s Primary Key
  • a join table – tourn_team_mm – has been generated to resolve the many-to-many relationship

Using options on the right-click menu, or even just dragging objects around, you can adjust the diagram to be more to your liking.
In this case I’ve used the Layout/Resize Objects to Visible option as well so that we can see everything for each table :


Before we can turn this into physical database objects, we probably want to do a bit of tweaking…

Refining the Relational Model Templates

First of all, we want to make some changes to our table and column names.
By convention, Entity names in a Logical model are singular, but once they become tables, they become plural.
This may not be a convention you necessarily feel obliged to follow, but it does give me the chance to demonstrate one method of changing this in the Data Modeler.
The other thing we want to do is to change column names which include a table name( e.g. the TOURNAMENT_ID column in the TOURNAMENT table), to use an abbreviation instead.
To do this, we first need to edit the template used to generate names for these objects.
In the Data Modeler Tree, right-click the Design and select Properties.

Expand the tree in the left-hand side of the pop-up window and you should get to the Templates :

Using the Add Variable buttons we can amend these templates to look something like this :

I’ve also specified abbreviations for each of the tables. For example :

In order to implement our improved object naming we need to right-click the relational model and then click Apply Naming Standards to Keys and Constraints

Hit OK and…

So, the TOURNAMENT_ID column has now been renamed to TOURN_ID. However, there are still some things that I’ll need to change manually ( probably because I haven’t figured out the proper way to do it).
Bear with me, I’ll be back in a minute…

Right, that’s better…

Now, let’s sort out those table names.

Transformation Scripts

First off, I’d like to make all of my table names uppercase.

One way of doing this, as described by Heli, is to use a pre-supplied transformation script.
To do this, go to the Tools menu and select Data Modeler/Design Rules and Transformations/Transformations.
Then simply select the appropriate script – in this case – Tables to upper case – Rhino and hit Apply.

The table names should now show in uppercase on this diagram.

I also wanted to pluralize the names ( e.g. COMPETITIONS rather than COMPETITION).

Whilst there are a number of ways to do this, we’re going to do a little light hacking to produce our own Transformation script to accomplish this.

So, back to the menu and select Tools/Data Modeler/Design Rules and Transformations/Transformations.

If I hit the Green Plus button, I can then add a script of my own.

Now, I know that my model contains table names which can all be pluralized simply by adding an “S”. I also know that this will not make any of the table names too long ( i.e. over 30 characters).
This makes the script fairly simple.

tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 name = table.getName()+"S"
 table.setName(name);
 table.setDirty(true);
}

I’ve named the script Append upper case ‘S’ to table names. I’ve also specified the object as relational and the engine as Oracle Nashorn.
Finally, I hit Save :

Now hit the Apply button and…

Table DDL Transformation Script

We’re almost ready to generate the DDL scripts to translate our Relational Model into a Physical one.
As I’m planning to use Editions in this application so I need to make sure that each table has an Editioning View created.

So, once more unto the Tools/Data Modeler/Design Rules and Transformations menu and this time we select Table DDL Transformation Scripts.

If we then navigate to the After Create scripts for the Journal Tables, we come across a script that we can plaigarise…

We need to create a new Script Set by clicking the Green Plus button, filling out some details and then, in the After Create section, adding the following code :

var ddl;
var lname;
//Editions Based Redefinition View suffix
ebrSuf = "#EBR";
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
	if(useSchema){
	     lname = tableProxy.getLongName();
	}else{
		lname = tableProxy.getName();
	}
}else{
	if(useSchema){
	     lname = table.getLongName();
	}else{
		lname = table.getName();
	}
}
if(prompt){
	ddl= "PROMPT Creating Editioning View for '"+lname+"';\n";
}else{
	ddl = "";
}
ddl = ddl + "create or replace editioning view "+lname+ebrSuf+" as select * from "+lname+";\n"
ddlStatementsList.add(new java.lang.String(ddl));

Finally, Save the new Script.

Now, we can test our script on a single table ( in this case COMPETITIONS), by clicking the Test button :

Generating DDL from a Relational Model

Let’s find out if our Relational Model is ready to go out into the big wide world.
With the Relational Model Diagram in focus, hit the Generate DDL toolbar button.

Now click Generate Button.

Go to Include Table DDL scripts tab and select our script from the drop-down and ensure all tables are checked

Click OK and…

We can see that Data Modeler has found a couple of errors. Taking a closer look at the script it’s generated we can find the culprits :

The easiest way to fix this is to edit the script directly before hitting Save.

At last, we have a script containing the DDL statements required to deploy our relational model to a real database.

Conclusion

I realise that this has not been so much a stroll through the well-kept garden as a hack through the undergrowth of Data Modeler in it’s integrated form.
Hopefully though, it’s enough for you to consider exploring further.
After all, a well-designed relational data model is the bedrock of a good Oracle Database Application and this is the tool that could well help you achieve this…and at minimal cost.

utPLSQL 3.0 – How to have your cake and eat it

Wed, 2018-04-25 14:13

“You can’t have your cake and eat it !” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations.
They also seem to be a bit intolerant of “cherry picking”.
I’ve never really understood the saying, “You can’t have your cake and eat it”.
What’s the point in having the cake unless you are going to eat it ?
Fortunately, I’m not alone in my perplexity – just ask any Brexiteer member of the British Cabinet.
For those who want to make sense of it ( the saying, not Brexit), there is a handy Wikepedia page that explains all.

When it comes to Unit Testing frameworks for PL/SQL, compromise between cake ownership and consumption is usually required.
Both utPLSQL 2.0 and ruby-plsql-spec have their good points, as well as some shortcomings.
Of course, if you want a more declarative approach to writing Unit Tests, you can always use TOAD or SQLDeveloper’s built-in tools.

Recently, a new player has arrived on the PL/SQL testing scene.
Despite it’s name, utPLSQL 3.0 appears to be less an evolution of utPLSQL 2.0 as a new framework all of it’s own.
What I’m going to do here, is put utPLSQL 3.0 through it’s paces and see how it measures up to the other solutions I’ve looked at previously.
Be warned, there may be crumbs…

Installation and Setup

If you’re comfortable on the command line, you can follow the instructions in the utPLSQL 3.0 documentation.
On the other hand, if you’re feeling old-fashioned, you can just head over to the Project’s GitHub page and download the latest version.
At the time of writing this is 3.0.4.

The downloaded file is utPLSQL.zip.

Now to unzip it. In my case, on Ubuntu, things look like this…

unzip utPLSQL.zip
Archive:  utPLSQL.zip
980af88b62c3c75b11a8f81d6ad96d1c835021b8
   creating: utPLSQL/
  inflating: utPLSQL/CONTRIBUTING.md  
  inflating: utPLSQL/LICENSE         
 extracting: utPLSQL/VERSION         
   creating: utPLSQL/docs/
...
***snip***
...
   creating: utPLSQL/test/ut_suite_manager/
  inflating: utPLSQL/test/ut_suite_manager/test_suite_manager.pkb  
  inflating: utPLSQL/test/ut_suite_manager/test_suite_manager.pks  
   creating: utPLSQL/test/ut_utils/
  inflating: utPLSQL/test/ut_utils/test_ut_utils.pkb  
  inflating: utPLSQL/test/ut_utils/test_ut_utils.pks  

The archive will have unzipped into a directory called utPLSQL.

We now have some decisions to make in terms of how we want to install the framework.
To save a bit of time, I’m going to go with the default.
Essentially this is :

DBMS_PROFILER is used by the framework to provide testing coverage statistics, more of which later.

Note that the documentation includes setup steps that provide you with a bit more control. However, if you’re happy to go with the default then you simply need to run the appropriate script as a user connected as SYSDBA…

cd utPLSQL/source
sqlplus berrym@bakeoff_tent as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 6 17:26:37 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @install_headless.sql

no rows selected

Creating utPLSQL user UT3
--------------------------------------------------------------
Installing utPLSQL v3 framework into UT3 schema
--------------------------------------------------------------
Switching current schema to UT3
--------------------------------------------------------------
Installing component UT_DBMS_OUTPUT_CACHE
Installing component UT_EXPECTATION_PROCESSOR
--------------------------------------------------------------
...
***snip***
...

Installing PLSQL profiler objects into UT3 schema
PLSQL_PROFILER_RUNS table created
PLSQL_PROFILER_UNITS table created
PLSQL_PROFILER_DATA table created
Sequence PLSQL_PROFILER_RUNNUMBER created
Installing component UT_FILE_MAPPER
--------------------------------------------------------------
...
***snip***
...
Synonym created.


Synonym created.


Synonym created.

We should now have a schema called UT3 which owns lots of database objects…

select object_type, count(*)
from dba_objects
where owner = 'UT3'
group by object_type
order by object_type
/
  
OBJECT_TYPE   COUNT(*)  
-----------   ---------
INDEX         13        
LOB            1         
PACKAGE       16        
PACKAGE BODY  16        
SEQUENCE       3         
SYNONYM       13        
TABLE          9         
TYPE          71        
TYPE BODY     53        
VIEW           2         


10 rows selected. 

One subtle difference that you may notice between utPLSQL 3.0 and it’s predecessor is the fact that the default application owner schema has a fairly “modest” set of privileges :

select privilege
from dba_sys_privs
where grantee = 'UT3'
/

PRIVILEGE         
---------
CREATE SESSION    
CREATE TYPE       
CREATE VIEW       
CREATE SYNONYM    
CREATE SEQUENCE   
CREATE PROCEDURE  
CREATE TABLE      
ALTER SESSION     


8 rows selected. 

However, the default password for this account is known…

SQL> connect ut3/XNtxj8eEgA6X6b6f@centos_xe
Connected.
SQL> show user
USER is "UT3"
SQL> 

Whilst it’s true that, as a testing framework, utPLSQL should be deployed only in non-production environments you may nevertheless find it prudent to lock the account immediately after installation…

alter user ut3 account lock
/

…and possibly even change the password for good measure.

Annotations and Matchers

There are two main component types in a utPLSQL 3.0 unit test – Annotations and Matchers.

Annotations allow the framework to identify packaged procedures as tests and (if required), group them into suites. This obviates the need for separate storage of configuration information.
Matchers are used to validate the results from a test execution.

This explanation would probably benefit from an example…

create or replace package ut3_demo
as
    -- %suite(Demonstrate Framework)
    
    -- %test(Will always pass)
    procedure perfect_cake;
    
    -- %test( Will always fail)
    procedure dontlike_cake;
  
end ut3_demo;  
/

The package begins with the suite annotation to identify it as a package that contains unit tests.

-- %suite(Demonstrate Framework)

The text in brackets displays when the test suite is executed.
The positioning of this annotation is important. It needs to be the first thing in the package after the CREATE OR REPLACE statement.
Also, as it’s a package level annotation, it needs to have one or more blank lines between it and any procedure level annotations.

Each of the procedures in the package is identified as an individual test

-- %test(Will always pass)
-- %test( Will always fail)

Once again the text will display when the test is executed.

In the package body, we can see the matchers come into play :

create or replace package body ut3_demo
as

    procedure perfect_cake is
    begin
        ut.expect( 1).to_( equal(1) );
    end;
    
    procedure dontlike_cake is
    begin
        ut.expect(1, 'Oops').to_( equal(0) );
    end;
end ut3_demo;
/

First impressions are that the code seems to have more in common with ruby-plsql-spec than it does with utPLSQL 2.0.
This impression is re-enforced when we execute the tests…

I was going to re-introduce the Footie app at this point as I’ve used it to demonstrate all of the other PL/SQL testing frameworks I’ve looked at so far.
However, in these unprecedented times, I feel that an unprecedented (and very British) example is called for.
Therefore, I humbly present…

The Great Brexit Bake-Off Application

The application owner is one hollywoodp ( the observant among you will have already noticed that Mary Berry is the DBA)…

The application consists of some tables :

alter session set current_schema = hollywoodp
/

create table brexit_bake_off
(
    id number primary key,
    contestant varchar2(100),
    show_stopper varchar2(100),
    notes varchar2(4000)
)
/

--
-- Create an error table for bulk loads - ERR$_BREXIT_BAKE_OFF
--
exec dbms_errlog.create_error_log('brexit_bake_off');


--
-- External table for ETL process to upload records to the application
--
create table contestants_xt
(
    id number,
    contestant varchar2(100),
    show_stopper varchar2(100),
    notes varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'contestants.bad'
            logfile 'contestants.log'
            skip 1
            fields terminated by '|'
            (
                id integer external,
                contestant char(100),
                show_stopper char(100),
                notes char(4000)
            )
        )    
        location ( 'contestants.csv')
    )
    reject limit unlimited
/

…and a package :

create or replace package bake_off as
    procedure add_contestant( 
        i_id number,
        i_contestant varchar2,
        i_show_stopper varchar2,
        i_notes varchar2 default null);

    function get_contestant_id( i_contestant varchar2)
        return number;

    function get_show_stopper( i_id number)
        return varchar2;

    procedure list_contestants( io_contestant_list in out sys_refcursor);

    procedure upload_contestants;
end bake_off;
/

create or replace package body bake_off as
    procedure add_contestant( 
        i_id number,
        i_contestant varchar2,
        i_show_stopper varchar2,
        i_notes varchar2 default null)
    is
    begin
        insert into brexit_bake_off( id, contestant, show_stopper, notes)
        values( i_id, i_contestant, i_show_stopper, i_notes);
    end add_contestant;    

    function get_contestant_id( i_contestant varchar2)
        return number
    is
        l_rtn number;
    begin
        select id
        into l_rtn
        from brexit_bake_off
        where upper(contestant) = upper(i_contestant);
        
        return l_rtn;
    exception when no_data_found then
        raise_application_error(-20900, 'This contestant is not in The Tent at the moment.');
    end get_contestant_id;
    
    function get_show_stopper( i_id number)
        return varchar2
    is
        l_rtn varchar2(100);
    begin
        select show_stopper
        into l_rtn
        from brexit_bake_off
        where id = i_id;
        
        return l_rtn;
    exception when no_data_found then
        raise_application_error(-20901, 'Soggy Bottom Error !');
    end get_show_stopper;    
        
    procedure list_contestants( io_contestant_list in out sys_refcursor)
    is
    begin
        open io_contestant_list for
        select id, contestant, show_stopper, notes
        from brexit_bake_off
        order by id;
    end list_contestants;
    
    procedure upload_contestants
    is
    begin
        insert into brexit_bake_off( id, contestant, show_stopper, notes)
        select id, contestant, show_stopper, notes
        from contestants_xt
        log errors reject limit unlimited;
    end upload_contestants;    
end bake_off;
/

Now, whilst Mr Hollywood is a renowned TV Chef, his PL/SQL coding skills do leave a little to be desired.
Also, the application in it’s current state is just about the minimum he could come up with to demonstrate the framework, which is, after all, why we’re here.
Therefore, I’d ask you to overlook the lack of anchored declarations etc. because, before we put the oven on, we need to make a fairly important design decision.

Where should I put my tests ?

According to the documentation, the default for utPLSQL is to have the tests located in the same schema as the code they are to run against. However, you may well have good reasons for wanting to keep the tests in a separate schema.

For one thing, you may want to ensure that the process to promote your codebase through to Test and Production environments remains consistent and that you don’t have to worry about taking specific steps to ensure that your test code ends up somewhere it shouldn’t.
Additionally, you may find it useful to create “helper” packages for your unit tests. These packages won’t themselves contain tests but will need to be treated as part of your test codebase rather than the application codebase.

If you decide to go down this route with utPLSQL, then you will have to ensure that the schema that owns your tests has the CREATE ANY PROCEDURE privilege if you want to avail yourself of the code coverage reporting provided by the framework.

This privilege does not need to be granted if the application owning schema also holds the tests.

I really would prefer to have my tests in an entirely separate schema. So, I’ve created this schema as follows :

set verify off
accept passwd prompt 'Enter password for UTP_BAKEOFF : ' hide

create user utp_bakeoff identified by &passwd
    default tablespace users
    temporary tablespace temp
/

grant create session, create view, create sequence, create table,  
    create any procedure to utp_bakeoff
/

alter user utp_bakeoff quota unlimited on users
/

--
-- Application specific grants required to generate test file for data load to
-- external table

grant read, write on directory my_files to utp_bakeoff
/

grant execute on utl_file to utp_bakeoff
/

The test schema also requires privileges on all of the Application’s database objects :

set serveroutput on size unlimited

declare
    l_priv varchar2(30);
begin
 
    for r_object in
    (
        select object_name, object_type
        from dba_objects
        where owner = 'HOLLYWOODP'
        and object_type in ('PACKAGE', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'VIEW')
    )
    loop
        l_priv :=
        case r_object.object_type
            when 'PACKAGE' then 'EXECUTE'
            when 'PROCEDURE' then 'EXECUTE'
            when 'TABLE' then 'ALL'
            else 'SELECT'
        end;
        dbms_output.put_line('Granting '||l_priv||' on '||r_object.object_name);
        execute immediate 'grant '||l_priv||' on hollywoodp.'||r_object.object_name||' to UTP_BAKEOFF';
    end loop;
end;
/

Run this and we get …

Granting ALL on BREXIT_BAKE_OFF
Granting EXECUTE on BAKE_OFF
Granting ALL on ERR$_BREXIT_BAKE_OFF
Granting ALL on CONTESTANTS_XT

Finally, we’re ready to start testing our application…

Testing Single Row Operations

First, we’re going to write some tests for the BAKE_OFF.ADD_CONTESTANT procedure. So, in the utp_bakeoff schema, we create a package

create or replace package add_contestant_ut
as

    -- %suite(add_contestant)      
    -- %suitepath(brexit_bake_off.bake_off)
    
    -- helper function to generate a single contestant record
    function setup_contestant return hollywoodp.brexit_bake_off%rowtype;
    
    -- %test(Add a new contestant)
    procedure add_contestant;
    
    -- %test( Add existing contestant)
    procedure add_duplicate_contestant;
end add_contestant_ut;
/

Before we take a look at the package body, it’s worth pausing to take note of the %suitepath annotation.
This Annotation allows separate test packages to be grouped together. In this instance, I’ve defined the path as Application Name/Package Name.
Note that if you want to use this annotation then it must be on the line directly after the %suite annotation in the package header. Otherwise utPLSQL won’t pick it up.

Now for the test package body…

create or replace package body add_contestant_ut
as

    function setup_contestant return hollywoodp.brexit_bake_off%rowtype
    is
        rec_contestant hollywoodp.brexit_bake_off%rowtype;
    begin
        select nvl(max(id), 0) + 1 as id,
            'David Davis' as contestant,
            'Black Forest Gateaux' as show_stopper,
            'Full of cherries to pick' as notes
        into rec_contestant
        from hollywoodp.brexit_bake_off;
        
        return rec_contestant;
    end setup_contestant;    
    
    
    function contestant_exists( i_id in number)
        return boolean
    is
        dummy pls_integer;
    begin
        select 1
        into dummy
        from hollywoodp.brexit_bake_off
        where id = i_id;
        
        return true;
    exception when no_data_found then return false;
    end contestant_exists;
    
    -- %test(Add a new contestant)
    procedure add_contestant is
    
        rec_contestant hollywoodp.brexit_bake_off%rowtype;
    begin
        -- Test setup phase
        rec_contestant := setup_contestant;
        
        -- Test execution
        hollywoodp.bake_off.add_contestant( 
            i_id => rec_contestant.id, 
            i_contestant => rec_contestant.contestant,
            i_show_stopper => rec_contestant.show_stopper,
            i_notes => rec_contestant.notes);
            
        -- Verify result
        ut.expect( contestant_exists(rec_contestant.id)).to_( be_true() );
    end add_contestant;
    
    -- %test( Add existing contestant)
    procedure add_duplicate_contestant is
        
        rec_contestant hollywoodp.brexit_bake_off%rowtype;
    begin
        -- Test setup phase
        rec_contestant := setup_contestant;
        insert into hollywoodp.brexit_bake_off( id, contestant, show_stopper, notes)
        values( rec_contestant.id, rec_contestant.contestant, rec_contestant.show_stopper, rec_contestant.notes);
        
        -- Test execution - use a nested block as we're expecting an error...
    
        begin
            hollywoodp.bake_off.add_contestant( 
                i_id => rec_contestant.id, 
                i_contestant => rec_contestant.contestant,
                i_show_stopper => rec_contestant.show_stopper,
                i_notes => rec_contestant.notes);
            -- Validation
            ut.fail('Expected unique key violation error but none raised');
        exception when others then
            ut.expect( sqlcode).to_( equal( -1));
        end;
    end add_duplicate_contestant;
    
end add_contestant_ut;
/

The structure of the tests is quite familiar in that there are four distinct phases, the first three of which are explicit :

  • Setup – prepare the system for the test
  • Execute – run the code to be tested
  • Verify – check the result
  • Teardown – reset the system to the state it was in prior to the test being run

Note that, in this instance, we are using the default behaviour of the framework for the teardown. This involves a savepoint being automatically created prior to each test being run and a rollback to that savepoint once the test completes. Later on, we’ll have a look at circumstances where we need to handle the Teardown phase ourselves.

The first test – add_contestant – uses a helper function and a boolean matcher :

ut.expect( contestant_exists(rec_contestant.id)).to_( be_true() );

The second test is checking both that we get an error when we try to add a duplicate record and that the error returned is the one we expect, namely :

ORA-00001: unique constraint (constraint_name) violated

As we’re expecting the call to the application code to error, we’re using a nested block :

begin
    hollywoodp.bake_off.add_contestant( 
        i_id => rec_contestant.id, 
        i_contestant => rec_contestant.contestant,
        i_show_stopper => rec_contestant.show_stopper,
        i_notes => rec_contestant.notes);
    -- Validation
    ut.fail('Expected unique key violation error but none raised');
exception when others then
    ut.expect( sqlcode).to_( equal( -1));
end;

If we now run the test, we can see that our code works as expected.

Incidentally, we can also see how utPLSQL recognises the hierarchy we’ve defined in the suitepath.

Whilst this approach works just fine for single-row operations, what happens when the framework is confronted with the need for …

Testing Ref Cursor values

This is always something of an ordeal in PL/SQL test frameworks – at least all of the ones I’ve looked at up until now. Fortunately utPLSQL’s equality matcher makes testing Ref Cursors as simple as you feel it really should be…

create or replace package list_contestants_ut as

    -- %suite(list_contestants)      
    -- %suitepath(brexit_bake_off.bake_off)

    -- %test( List all the contestants)
    procedure list_contestants;
end list_contestants_ut;
/

create or replace package body list_contestants_ut as
    procedure list_contestants
    is
        l_rc_expected sys_refcursor;
        l_rc_actual sys_refcursor;

    begin
    
        -- setup
        insert into hollywoodp.brexit_bake_off 
        with recs as
        (
            select nvl(max(id), 0) + 1 as id, 
                'David Davis' as contestant, 
                'Black Forest Gateau' as show_stopper, 
                'Lots of cherries' as notes 
            from hollywoodp.brexit_bake_off
            union all
            select nvl(max(id), 0) + 2, 
                'Michel Barnier', 
                'Chocolate Eclair', 
                'No cherries to pick' 
            from hollywoodp.brexit_bake_off
            union all
            select nvl(max(id), 0) + 3, 
                'Jacob Rees-Mogg', 
                'Victoria Sponge', 
                'Traditional and no need for cherries'
            from hollywoodp.brexit_bake_off
            union all
            select nvl(max(id), 0) + 4, 
                'Tony Blair', 
                'Jaffa Cake', 
                'Definitely not a biscuit and a new referendum is required to settle this'
            from hollywoodp.brexit_bake_off
        )
            select * from recs;
    
        -- Get expected results
        open l_rc_expected for 
            select id, contestant, show_stopper, notes 
            from hollywoodp.brexit_bake_off
            order by 1;

        -- execute
        hollywoodp.bake_off.list_contestants(l_rc_actual);
    
        -- Verify
        ut.expect( l_rc_actual).to_equal( l_rc_expected);
        close l_rc_actual;
        close l_rc_expected;
    end list_contestants;

end list_contestants_ut;
/

Run this and we get :

Incidentally, you may notice that the call to ut.run in this instance is a little different to what I was using previously.
There are a number of ways to execute one or more utPLSQL tests through the ut.run procedure and we’ll be taking a look at some of these in a little while.

Testing across Transaction Boundaries

In this case, we’re testing the bulk upload of records from a file into the application tables via an external table.
The load itself makes use of the LOG ERRORS clause which initiates an Autonomous Transaction in the background.
This means we’re going to need to handle the teardown phase of the tests ourselves as utPLSQL’s default rollback-to-savepoint operation will not do the job.

First of all, here’s a quick reminder of the BAKE_OFF.UPLOAD_CONTESTANTS procedure that we want to test :

...
procedure upload_contestants
is
begin
    insert into brexit_bake_off( id, contestant, show_stopper, notes)
    select id, contestant, show_stopper, notes
    from contestants_xt
    log errors reject limit unlimited;
end upload_contestants;    
...

As part of the setup and teardown for the test, we’ll need to do a number of file operations – i.e.

  • backup the existing data file
  • create a test file for the external table
  • remove the test file
  • move the original file (if any) back into place

As we may have other loads we want to test this way in the future, then it would seem sensible to separate the code for these file operations into a helper package :

create or replace package test_file_utils as

    function file_exists( i_dir all_directories.directory_name%type, i_fname varchar2)
        return boolean;
        
    procedure backup_file( i_dir all_directories.directory_name%type, i_fname varchar2, o_backup_fname out varchar2);

    procedure revert_file( i_dir all_directories.directory_name%type, i_fname varchar2, i_backup_fname varchar2);
end test_file_utils;
/

create or replace package body test_file_utils as

    function file_exists( i_dir all_directories.directory_name%type, i_fname varchar2)
        return boolean
    is
        fh utl_file.file_type;
        e_no_file exception;
        -- ORA-29283 is returned if file does not exist or is not accessible.
        -- If the latter then the whole thing will fall over when we try to overwrite it.
        -- For now then, we can assume that this error means "file does not exist"
        pragma exception_init(e_no_file, -29283);
    begin
        fh := utl_file.fopen( i_dir, i_fname, 'r');
        utl_file.fclose(fh);
        return true;
    exception when e_no_file then
        return false;
    end file_exists;
    
    procedure backup_file( 
        i_dir all_directories.directory_name%type, 
        i_fname varchar2, 
        o_backup_fname out varchar2)
    is
        backup_fname varchar2(100);
    begin
        backup_fname := i_fname||systimestamp||'.bak';
        utl_file.frename( i_dir, i_fname, i_dir, backup_fname);
        o_backup_fname := backup_fname;
    end backup_file;
    
    procedure revert_file( 
        i_dir all_directories.directory_name%type, 
        i_fname varchar2, 
        i_backup_fname varchar2)
    is
    begin
        -- delete i_fname - the file created for the test
        utl_file.fremove(i_dir, i_fname);
        -- if a backup filename exists then put it back
        if i_backup_fname is not null then
            utl_file.frename( i_dir, i_backup_fname, i_dir, i_fname);
        end if;
    end revert_file;
end test_file_utils;
/

Remember, as we’ve decided to hold all of our test code in a separate schema, we don’t have to worry about distinguishing this package from the application codebase itself.

Now for the test. In the package header, we’re using the rollback annotation to let utPLSQL know that we’ll look after the teardown phase manually for any test in this package :

create or replace package upload_contestants_ut as
    
    -- %suite(upload_contestants)     
    -- %rollback(manual)
    -- %suitepath(brexit_bake_off.bake_off)
    
    -- %test( bulk_upload_contestants)
    procedure upload_contestants;
end upload_contestants_ut;
/

Now for the test code itself. There’s quite a bit going on here.
In the setup phase we :

  • backup the target application table and it’s associated error table
  • generate the file to be uploaded
  • populate ref cursors with the expected results

In the verification phase, we use the to_equal matcher to compare the expected results refcursors with the actual results ( also ref cursors).

Finally, we re-set the application to it’s state prior to the test being executed by :

  • removing test records from the application and error tables
  • dropping the backup tables
  • tidying up the data files

All of which looks something like this :

create or replace package body upload_contestants_ut as

    --
    -- Private helper procedures
    --
    procedure backup_tables is

        pragma autonomous_transaction;

    begin
                
        execute immediate 'create table brexit_bake_off_bu as select rowid as bu_rowid, tab.* from hollywoodp.brexit_bake_off tab';
        execute immediate 'create table err$_brexit_bake_off_bu as select rowid as bu_rowid, tab.* from hollywoodp.err$_brexit_bake_off tab';
    end backup_tables;
    
    procedure create_contestant_file( i_dir all_directories.directory_name%type, i_fname varchar2)
    is
        fh utl_file.file_type;
    begin
        fh := utl_file.fopen(i_dir, i_fname, 'w');
        utl_file.put_line(fh, 'id|contestant|show_stopper|notes');
        for r_contestant in
        (
            select nvl(max(id), 0) + 1||chr(124)||'David Davis'
                ||chr(124)||'Black Forest Gateau'||chr(124)||null||chr(124) as rec
            from hollywoodp.brexit_bake_off 
            union all
            select nvl(max(id), 0) + 2||chr(124)||'Michel Barnier'
                ||chr(124)||'Chocolate Eclair'||chr(124)||'Leave my cherries alone !'||chr(124)
            from hollywoodp.brexit_bake_off
            union all
            -- Duplicate records (by ID)
            select nvl(max(id), 0) + 1||chr(124)||'Jacob Rees-Mogg' 
                ||chr(124)||'Victoria Sponge'||chr(124)||null||chr(124)
            from hollywoodp.brexit_bake_off
            union all
            select nvl(max(id), 0) + 2||chr(124)||'Tony Blair'
                ||chr(124)||'Jaffa Cakes'||chr(124)||'Tough on brexit, tough on the causes of Brexit'||chr(124)
            from hollywoodp.brexit_bake_off
        )
        loop
            utl_file.put_line(fh, r_contestant.rec);
        end loop;
        utl_file.fflush(fh);
        utl_file.fclose(fh);
    end create_contestant_file;
    
    procedure cleardown_test_records is
    
        pragma autonomous_transaction;
    begin
        execute immediate 
            'delete from hollywoodp.brexit_bake_off
            where rowid not in (select bu_rowid from brexit_bake_off_bu)';
        
        execute immediate
            'delete from hollywoodp.err$_brexit_bake_off
            where rowid not in (select bu_rowid from err$_brexit_bake_off_bu)';
            
        commit;    
        
    end cleardown_test_records;
    
    procedure drop_backup_tables is
    
        pragma autonomous_transaction;
    begin
    
        execute immediate 'drop table brexit_bake_off_bu';
        execute immediate 'drop table err$_brexit_bake_off_bu';
    end drop_backup_tables;
    
    -- The test itself
    procedure upload_contestants
    is
    
        target_dir constant all_directories.directory_name%type := 'MY_FILES';
        fname constant varchar2(100) := 'contestants.csv';
        
        backup_fname varchar2(100);
        
        expected_load sys_refcursor;
        expected_err sys_refcursor;
        actual_load sys_refcursor;
        actual_err sys_refcursor;
    begin
        --
        -- Setup Phase
        --
        backup_tables;
        -- Backup the external table file 
        if test_file_utils.file_exists( target_dir, fname) then
            test_file_utils.backup_file( target_dir, fname, backup_fname);
        end if;
        -- Create a load file
        create_contestant_file( target_dir, fname);
        
        -- Populate the expected results
        open expected_load for 
            select *
            from hollywoodp.contestants_xt
            where upper(contestant) in ('DAVID DAVIS', 'MICHEL BARNIER')
            order by id;
            
        open expected_err for    
            select 1 as ora_err_number$, 
                id, contestant, show_stopper, notes 
            from hollywoodp.contestants_xt
            where upper( contestant) in ('JACOB REES-MOGG', 'TONY BLAIR')
            order by id;
            
        --    
        -- Execute
        --
        hollywoodp.bake_off.upload_contestants;
        
        --
        -- Verify
        --
        open actual_load for
            select *
            from hollywoodp.brexit_bake_off
            order by id;
            
        open actual_err for
            select ora_err_number$, 
                id, contestant, show_stopper, notes
            from hollywoodp.err$_brexit_bake_off
            order by id;
            
        ut.expect( expected_load).to_equal( actual_load);
        ut.expect( expected_err).to_equal( actual_err);
        
        --
        -- Teardown
        --
        cleardown_test_records;
        drop_backup_tables;
        test_file_utils.revert_file( target_dir, fname, backup_fname);

    end upload_contestants;
end upload_contestants_ut;
/
Running tests and reporting results

The framework does offer an API for use to execute tests programatically. However, whilst you’re writing the tests themselves, you’ll probably want something a bit more interactive.

You can simply run all of the tests in the current schema as follows :

set serveroutput on size unlimited
exec ut.run

However, there are times when you’ll probably need to be a bit more selective.
Therefore, it’s good to know that utPLSQL will let you execute tests interactively in a number of different ways :

set serveroutput on size unlimited

alter session set current_schema = utp_bakeoff
/

-- single test passing in (package name.procedure name)
exec ut.run('add_contestant_ut.add_contestant')

-- all tests in a package (package name)
exec ut.run('add_contestant_ut')

-- all suites in a suitepath (owning schema:suitepath) 
exec ut.run('utp_bakeoff:brexit_bake_off.bake_off')

If we run this for the application tests we’ve written, the output looks like this :

By default ut_run uses the ut_document_reporter to format the output from the tests.
However, there are other possible formats, which you can invoke with a second argument to UT_RUN.

For example…

exec ut_run('add_contestant_ut', ut_xunit_reporter());

…outputs…

<testsuites tests="2" skipped="0" error="0" failure="0" name="" time=".002972" >
<testsuite tests="2" id="1" package="brexit_bake_off"  skipped="0" error="0" failure="0" name="brexit_bake_off" time=".002894" >
<testsuite tests="2" id="2" package="brexit_bake_off.bake_off"  skipped="0" error="0" failure="0" name="bake_off" time=".002859" >
<testsuite tests="2" id="3" package="brexit_bake_off.bake_off.add_contestant_ut"  skipped="0" error="0" failure="0" name="add_contestant" time=".00279" >
<testcase classname="brexit_bake_off.bake_off"  assertions="1" skipped="0" error="0" failure="0" name="Add a new contestant" time=".001087" >
</testcase>
<testcase classname="brexit_bake_off.bake_off.add_contestant_ut"  assertions="1" skipped="0" error="0" failure="0" name="Add existing contestant" time=".001175" >
</testcase>
</testsuite>
</testsuite>
</testsuite>
</testsuites>

By contrast, if you want something slightly more colourful…

set serveroutput on size unlimited
exec ut.run('add_contestant_ut', a_color_console => true)

…or even…

set serveroutput on size unlimited
exec ut.run(a_color_console => true)

Note that, unlike the previous executions, the a_color_console parameter is being passed by reference rather than position.

Provided your command line supports ANSICONSOLE, you are rewarded with…

Test Coverage reporting

As mentioned a couple of times already, utPLSQL does also provide coverage reporting functionality.
In this case, we’re going to look at the HTML report.

set serveroutput on size unlimited
alter session set current_schema = utp_bakeoff;
set feedback off
spool add_contestant_coverage.html
exec ut.run('add_contestant_ut', ut_coverage_html_reporter(), a_coverage_schemes => ut_varchar2_list('hollywoodp'))
spool off

Opening the file in a web browser we can see some summary information :

Clicking on the magnifying glass allows us to drill-down into individual program units :

Of course, you’ll probably want to get an overall picture of coverage in terms of all tests for the application code. In this case you can simply run :

set serveroutput on size unlimited
alter session set current_schema = utp_bakeoff;
set feedback off
spool brexit_bake_off_coverage.html
exec ut.run(ut_coverage_html_reporter(), a_coverage_schemes => ut_varchar2_list('hollywoodp'))
spool off

When we look at this file in the browser, we can see that at least we’ve made a start :

Keeping track of your Annotations

Whilst annotations provide a method of identifying and organising tests in a way that avoids the need for storing large amounts of metadata, it can be easy to “lose” tests as a result.
For example, if you have a fat-finger moment and mis-type a suitepath value, that test will not execute when you expect it to ( i.e. when you run that suitepath).

Fortunately, utPLSQL does keep track of the annotations under the covers, using the UT_ANNOTATION_CACHE_INFO and UT_ANNOTATION_CACHE tables. Despite their names, these are permanent tables :

So, if I want to make sure that I haven’t leaned on the keyboard at an inopportune moment, I can run a query like :

select aci.object_name, ac.annotation_text
from ut3.ut_annotation_cache_info aci
inner join ut3.ut_annotation_cache ac
    on ac.cache_id = aci.cache_id
    and ac.annotation_name = 'suitepath'
    and aci.object_owner = 'UTP_BAKEOFF'
order by 1
/

…which in my case returns…


OBJECT_NAME                    ANNOTATION_TEXT                         
------------------------------ ----------------------------------------
ADD_CONTESTANT_UT              brexit_bake_off.bake_off                
LIST_CONTESTANTS_UT            brexit_bake_off.bake_off                
UPLOAD_CONTESTANTS_UT          brexit_bake_off.bake_off                
Final Thoughts

I’ve tried to give some flavour of what the framework is capable of, but I’ve really just scratched the surface.
For more information, I’d suggest you take a look at the framework’s excellent documentation.

Also, Jacek Gebal, one of the authors of the framework has shared a presentation which you may find useful.

The utPLSQL 3.0 framework is a very different beast from it’s predecessor. The ground up re-write of the framework has brought it bang up to date in terms of both functionality and ease of use.
If you’re looking for a PL/SQL testing framework that’s contained entirely within the database then look no further…unless your allergic to cherries.

Streaming Videos with Plex on Raspberry Pi

Sun, 2018-04-08 05:52

The recent Bank Holiday weekend in England provided me with a perfect opportunity to get on with some D.I.Y.

We have a collection of movie files, which I’ve stored on an external USB hard-drive. At the moment, these files are only accessible from the smart TV it’s plugged into.
I want to be able to stream these movies to the various Connected Devices we have around the house.

Time, once again, to call on my trusty Raspberry Pi 2 b-spec, running on Raspbian Jessie.

What I’m going to do is :

  • Mount my USB Drive on the Pi
  • Install Plex Server on the Pi to facilitate streaming
  • Install Plex Client on relevant Connected Devices
  • Create a Library containing all of my movies
  • Stream a movie whilst I wait for it to stop raining

Hopefully after all that, I’ll be looking at something like this :

plex

Before we do any of that however, we need to get the USB drive to work on the Pi…

Hardware setup

The hard drive is powered through the USB port it’s connected to.
Unfortunately, the Pi does not provide sufficient power on it’s own.
Therefore, we need to use a powered USB hub to connect the two.

So, we plug the USB hub into the mains and then connect it to one of the USB ports on the Pi.
Then we plug the hard-drive into the hub :

pi_hub_hd.jpg

Now that’s done, we can get on with making the files on the hard-drive available to the Pi.

A word on Software versions

The first thing to note is that I’m connected as pi for all of the steps that follow.

It would also be helpful to know the version of Raspbian that’s running on the Pi.
One way of determining this is to run the following in a terminal :

cat /etc/os-release |grep 'PRETTY_NAME'

…which, in my case, returns :

PRETTY_NAME="Raspbian GNU/Linux 8 (jessie)"

If you are using a different version of Raspbian (e.g. Wheezy), you may need to amend some of the commands that follow to use the appropriate repository for your version.

Finally, before we go making any software changes, it’s probably a good idea to make sure that we’re all up-to-date on the Pi :

sudo apt-get update
sudo apt-get upgrade
Mounting the USB Drive on the Pi

In order to properly interact with the files on the external disk, we need to mount it.
First of all, we need to create a directory on which to mount the drive.

So, in the terminal…

cd /mnt
sudo mkdir usbstorage

sudo chown -R pi:pi /mnt/usbstorage
sudo chmod -R 775 /mnt/usbstorage

We should now have a directory that looks like this :

ls -l
total 4
drwxrwxr-x 2 pi pi 4096 Apr  1 13:52 usbstorage

Next, we need to know what type of filesystem the external disk is using.

To find this out we can run…

sudo blkid

Alternatively, if you only have a single USB device connected to your Pi, you can filter out some of the noise…

sudo blkid |grep /dev/sda1

In my case, I get back :

/dev/sda1: LABEL="Seagate Backup Plus Drive" UUID="EC26E71826E6E316" TYPE="ntfs" PARTUUID="945986dc-01"

The “TYPE=” part of the output identifies the file system type you will need to specify when mounting the drive.
The “/dev/sda1:” part of the output confirms that our disk is currently mounted as this device.

Note that you may need to install an additional package to be able to mount ntfs disks ( although I already had this on my Pi). To make sure, you can simply run :

sudo apt-get update
sudo apt-get install ntfs-3g -y

To test mounting the disk on the mount point we’ve just created…

sudo umount /dev/sda1
sudo mount /dev/sda1 /mnt/usbstorage

We should now be able to see the files on the disk…

mounted_dir

In order to make sure that the disk is mounted in this way every time the Pi starts, we need to add an entry to the /etc/fstab file.

One way to specify the device to mount is to use the UUID, which you can find by running :

sudo ls -l /dev/disk/by-uuid/

…and looking for your device name (in my case /sda1) :

lrwxrwxrwx 1 root root 15 Apr  1 13:49 0A23-A597 -> ../../mmcblk0p5
lrwxrwxrwx 1 root root 15 Apr  1 13:49 5a632192-64d9-41fd-afe4-55a4d570cd8c -> ../../mmcblk0p3
lrwxrwxrwx 1 root root 15 Apr  1 13:49 accadb67-46be-46e3-9573-62f80f0cb652 -> ../../mmcblk0p6
lrwxrwxrwx 1 root root 15 Apr  1 13:49 BB43-8E16 -> ../../mmcblk0p1
lrwxrwxrwx 1 root root 10 Apr  1 14:14 EC26E71826E6E316 -> ../../sda1

The UUID is the hexadecimal number at the start of the listing ( in this case EC26E71826E6E316).

Now we edit the fstab…

sudo nano /etc/fstab

…by adding the line…

UUID=EC26E71826E6E316    /mnt/usbstorage    ntfs   nofail,uid=pi,gid=pi    0   0

To make sure that you’ve entered everything correctly you can run :

sudo mount -a

If you get any errors from this command then you will need to check your fstab changes.
Otherwise, you can go ahead and re-boot by running the following ( NOTE – I’m doing this via an ssh session on another computer) :

sudo reboot

When the Pi comes back up, you should still be able to see the files on the disk in the /mnt/usbstorage directory.

Installing the Plex Server

First of all, we need to enable secure installations via https :

sudo apt-get install apt-transport-https -y

Now we need to get the key for the Plex download site…

wget -O -  https://dev2day.de/pms/dev2day-pms.gpg.key   | sudo apt-key add -

…and add the repository to the sources list on the Pi…

echo "deb https://dev2day.de/pms/ jessie main" | sudo tee /etc/apt/sources.list.d/pms.list

…which should output…

deb https://dev2day.de/pms/ jessie main

Now make sure that the plex server is included in sources…

sudo apt-get update

NOTE – this should produce some output similar to …

Get:1 http://mirrordirector.raspbian.org jessie InRelease [14.9 kB]
Hit http://archive.raspberrypi.org jessie InRelease  
Get:2 https://dev2day.de jessie InRelease [1,803 B]     
***edited for brevity***

Ign http://mirrordirector.raspbian.org jessie/rpi Translation-en_GB
Ign http://mirrordirector.raspbian.org jessie/rpi Translation-en
Fetched 9,683 kB in 32s (297 kB/s)
Reading package lists... Done

…and install it…

sudo apt-get install -t jessie plexmediaserver -y

…which produces output something like…

Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  plexmediaserver-installer
The following NEW packages will be installed:
  plexmediaserver plexmediaserver-installer
0 upgraded, 2 newly installed, 0 to remove and 16 not upgraded.
Need to get 8,468 B of archives.
After this operation, 0 B of additional disk space will be used.
Get:1 https://dev2day.de/pms/ jessie/main plexmediaserver-installer armhf 1.12.1.4885-1046ba85f-1 [7,520 B]
Get:2 https://dev2day.de/pms/ jessie/main plexmediaserver all 0.9.16.4.1911-ee6e505-2~jessie [948 B]
Fetched 8,468 B in 1s (7,776 B/s)   
Selecting previously unselected package plexmediaserver-installer.
(Reading database ... 132214 files and directories currently installed.)
Preparing to unpack .../plexmediaserver-installer_1.12.1.4885-1046ba85f-1_armhf.deb ...
Unpacking plexmediaserver-installer (1.12.1.4885-1046ba85f-1) ...
Selecting previously unselected package plexmediaserver.
Preparing to unpack .../plexmediaserver_0.9.16.4.1911-ee6e505-2~jessie_all.deb ...
Unpacking plexmediaserver (0.9.16.4.1911-ee6e505-2~jessie) ...
Processing triggers for systemd (215-17+deb8u7) ...
Setting up plexmediaserver-installer (1.12.1.4885-1046ba85f-1) ...
Downloading readynas package ...
######################################################################## 100.0%
Passed checksum test.
Extracting readynas.deb ...
rm: cannot remove ‘/etc/systemd/system/plexmediaserver.service.d/override.conf’: No such file or directory
Synchronizing state for plexmediaserver.service with sysvinit using update-rc.d...
Executing /usr/sbin/update-rc.d plexmediaserver defaults
Executing /usr/sbin/update-rc.d plexmediaserver disable
insserv: warning: current start runlevel(s) (empty) of script `plexmediaserver' overrides LSB defaults (2 3 4 5).
insserv: warning: current stop runlevel(s) (0 1 2 3 4 5 6) of script `plexmediaserver' overrides LSB defaults (0 1 6).
Synchronizing state for plexmediaserver.service with sysvinit using update-rc.d...
Executing /usr/sbin/update-rc.d plexmediaserver defaults
insserv: warning: current start runlevel(s) (empty) of script `plexmediaserver' overrides LSB defaults (2 3 4 5).
insserv: warning: current stop runlevel(s) (0 1 2 3 4 5 6) of script `plexmediaserver' overrides LSB defaults (0 1 6).
Executing /usr/sbin/update-rc.d plexmediaserver enable
Synchronizing state for plexmediaserver.service with sysvinit using update-rc.d...
Executing /usr/sbin/update-rc.d plexmediaserver defaults
Executing /usr/sbin/update-rc.d plexmediaserver enable
Setting up plexmediaserver (0.9.16.4.1911-ee6e505-2~jessie) ...

Now to re-start the pi…

sudo reboot
Configuring the Plex server

All of this can be done via the Plex web interface.

To confirm the web address for our newly installed Plex Server…

hostname -i

In my case I get :

127.0.1.1

Therefore, I’ll need to use the following URL to access the Plex Web Interface via Epiphany :

127.0.1.1:32400/web

Note – alternatively, you can access the web interface via another computer if your pi is accessible from the network.
If the network name is raspberrypi.local, you can use :

http://raspberrypi.local:32400/web

When you first land on the page you will be asked to sign in :

plex1

At this point you can either use an existing account, or create a new one specifically for Plex.

Note that if you’re using a Google account that you have not previously used from the Pi then Plex may “hang” silently until you verify that this is a legitimate connection ( i.e. Google drop a mail to your backup mail address for you to logon and verify the connection).

The reason you need an account is eloquently explained in the next screen – i.e. to get all of that lovely metadata to make your library look pretty …

plex2

Click the Got it button and Plex will then look for your Plex server…

I don’t want to access my media from outside of my home network, so I’ve left the box unchecked and hit Next

Yes, we do want to Add Library

…for Movies, so click the Movies icon and then Next

…and Browse for Media Folder

…where we point it at the disk we mounted earlier and click Add

…before finally, clicking Done.

Plex will now go through the files at the location you’ve specified and try to match up with the appropriate metadata. It seems to do this rather well, but it doesn’t always get it right.
We’ll come back to that shortly.

One final configuration step is to set the transcoder directory to be on the external disk, where there’s more space available than on the memory card in the Pi :

Now we need to install the Plex client on our connected devices…

Installing the Plex Client

Of necessity, this section is somewhat generic. I have two devices that I want to stream to. The first is a Samsung Smart TV running Tizen.
The second is an Amazon Fire TV stick plugged into an elderly – and definitely not very smart – Toshiba.

In each case, you need to go to the relevant App Store and download the Plex client onto the device.
When you first open Plex on your TV/Stick, you’ll be instructed to use a separate device to visit :

https://plex.tv/link

…and enter the displayed 4-digit code.

Once you do this, your device will be associated with your Plex account :

…and you will then be able to see the name of your Plex server on your device.

When Plex gets perplexed

If Plex has managed to mismatch meta data with a given film, you can persuade it to change it’s mind.

For example, the actual version of A Christmas Carol that’s on the disk is the 2009 animated version. However, Plex has matched to a different version.
To fix this, we can select Fix Match… from the menu

…and wait for Plex to present us with a list of alternatives….

…before we select the correct one…

There are, of course, several ways to do this but this method should work as a first attempt for most mismatches.

Useful Links

Addictive Tips have a rather good guide installing Plex on a Pi, in case you get stuck following this one ( or just prefer theirs).

There’s also a useful article on the Plex site about how to name your media files.

As for me, well it seems simply ages since I’ve sung along to Frozen…

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

Pages