Improving Wetware

Because technology is never the issue

PostgreSQL version of ERD Query for GraphViz

Posted by Pete McBreen Tue, 01 Nov 2016 02:42:00 GMT

Finally got around to doing the PostgreSQL version of the ERD generator using GraphViz. This uses the database from the PostgreSQL Tutorial site

As before there is a PostgreSQLERDGeneration.sql query, the resulting DVDRental.gv dot file for graphviz to generate the file and the resulting DVDRental.html. Inline interactive version of the diagram is below.

A few differences

  • Primary keys are sorted to the top of the table symbols
  • Lines are thicker on hover to make it easier to select the relevant symbol
  • Query does not filter out empty tables.

This completes the set of databases that I have made this work for, might include DB2 at some point in the future if I ever work on an IBM system.

For this interactive version, hovering over the lines makes them larger so that you can click to highlight the line. This makes it easy to plan out a query by following the links between the relevant tables, regardless of where they are on the screen. A good example of this would be tracing out which language DVDs are rented out in a specified city? This needs seven tables and six relationships to determine this, and it is much easier to have the path highlighted while writing the query than having to remember the path as you write the query.

dvdrental dvdrental.actor dvdrental.actor actor_id first_name last_name last_update dvdrental.film_actor dvdrental.film_actor actor_id film_id actor_id film_id last_update dvdrental.actor:actor_pkey->dvdrental.film_actor:film_actor_actor_id_fkey dvdrental.address dvdrental.address address_id city_id address address2 district postal_code phone last_update dvdrental.customer dvdrental.customer customer_id address_id store_id first_name last_name email activebool create_date last_update active dvdrental.address:address_pkey->dvdrental.customer:customer_address_id_fkey dvdrental.staff dvdrental.staff staff_id address_id first_name last_name email store_id active username password last_update picture dvdrental.address:address_pkey->dvdrental.staff:staff_address_id_fkey dvdrental.store dvdrental.store store_id address_id manager_staff_id last_update dvdrental.address:address_pkey->dvdrental.store:store_address_id_fkey dvdrental.category dvdrental.category category_id name last_update dvdrental.film_category dvdrental.film_category film_id category_id category_id film_id last_update dvdrental.category:category_pkey->dvdrental.film_category:film_category_category_id_fkey dvdrental.city dvdrental.city city_id country_id city last_update dvdrental.city:city_pkey->dvdrental.address:fk_address_city dvdrental.country dvdrental.country country_id country last_update dvdrental.country:country_pkey->dvdrental.city:fk_city dvdrental.payment dvdrental.payment payment_id customer_id rental_id staff_id amount payment_date dvdrental.customer:customer_pkey->dvdrental.payment:payment_customer_id_fkey dvdrental.rental dvdrental.rental rental_id customer_id inventory_id staff_id rental_date return_date last_update dvdrental.customer:customer_pkey->dvdrental.rental:rental_customer_id_fkey dvdrental.film dvdrental.film film_id language_id title description release_year rental_duration rental_rate length replacement_cost rating last_update special_features fulltext dvdrental.film:film_pkey->dvdrental.film_actor:film_actor_film_id_fkey dvdrental.film:film_pkey->dvdrental.film_category:film_category_film_id_fkey dvdrental.inventory dvdrental.inventory inventory_id film_id store_id last_update dvdrental.film:film_pkey->dvdrental.inventory:inventory_film_id_fkey dvdrental.inventory:inventory_pkey->dvdrental.rental:rental_inventory_id_fkey dvdrental.language dvdrental.language language_id name last_update dvdrental.language:language_pkey->dvdrental.film:film_language_id_fkey dvdrental.rental:rental_pkey->dvdrental.payment:payment_rental_id_fkey dvdrental.staff:staff_pkey->dvdrental.payment:payment_staff_id_fkey dvdrental.staff:staff_pkey->dvdrental.rental:rental_staff_id_key dvdrental.staff:staff_pkey->dvdrental.store:store_manager_staff_id_fkey

More "Things I won't work with" Chemistry Edition

Posted by Pete McBreen Mon, 03 Oct 2016 03:06:00 GMT

Amusing as ever, Derek Lowe’s take on This Compound needs some Hydrogen Peroxide

The experimental section of the paper is worth a read, and again, you can tell that Matzger’s group has good technique because everyone made it intact to the writing of the manuscript. There are pictures of the crystals themselves, which are very nice, until you realize that they’re plotting to blow you into the ceiling crawl space at the first opportunity. It says that “no unplanned detonations were encountered” during the work, which is a nice distinction.

Gravitational pull from Ice Sheets

Posted by Pete McBreen Sun, 18 Sep 2016 05:03:00 GMT

An intriguing presentation on the effects on sea level rise from ice sheet melting, primarily due to the gravitational pull of the large mass of the existing ice sheets

Taking the Fingerprints of Global Sea Level Rise

Spoiler Alert! Next to the ice sheets the sea level can actually fall as a result of the ice melting due to the loss of the gravitational pull from the mass of the ice sheet. It will fall even further over geological times due to the rebound of the crust when the weight of the ice is removed. Canada is rebounding approx. 1mm/yr in response to the removal of the ice sheets from the last ice age.

How to fail at Agile part 1

Posted by Pete McBreen Thu, 04 Aug 2016 02:45:00 GMT

In every iteration, have a few bugs that do not get fixed. After five or six iterations you can build up a reasonable size bug backlog without even trying, and the best bit is that you can hide them in the previous iterations so nobody important sees them.

Obvious fixes:

  • If there is anything left over in the current iteration, move it into the next and increase the priority of that item.
  • Review all items that overflow into the next iteration to make sure that the team understands what is needed.
  • Publish the failure up the management chain if a defect survives to iterations.

SqlServer version of Drawing ER Diagrams with GraphViz

Posted by Pete McBreen Sat, 19 Mar 2016 03:25:00 GMT

This is the same as the Oracle version, but works with SQL Server (tested on SqlServer 2012)

ERDCreationSqlServer-2012-populated.sql

Only problem I’ve identified is that the databases that most need a generated ERD often are lacking in foreign keys that this query uses to identify the relationships…

Drawing ER Diagrams with GraphViz

Posted by Pete McBreen Sat, 19 Dec 2015 22:46:00 GMT

Recently as part of an archaeology task of understanding how some SQL queries were working, I needed to draw an ERD to help with my understanding of the database. After contemplating drawing the diagram by hand for a few seconds, I decided to leverage GraphViz and just draw a diagram of all of the foreign key relationships between the tables.

Since it was an Oracle database, the queries to read the relationships were not that complex ErdCrearion-specific.sql is designed to run in SQLDeveloper and prompt for the :OWNER tablespace name to pick the tables from, and limit the selection to the names mentioned in the tablelist CTE (unfortunately duplicated as I have not rewritten this to make it simpler).

From that query the output is pasted into the starter GraphViz .gv file scottsimple.gv

digraph ERD {
  graph [   rankdir = "LR" ];
  ranksep=2;

  "SCOTT.DEPT" [ label="<SCOTT.DEPT> SCOTT.DEPT|<PK_DEPT>DEPTNO \l |DNAME \l LOC \l " shape = "record" ];
  "SCOTT.EMP" [ label="<SCOTT.EMP> SCOTT.EMP|<FK_DEPTNO>DEPTNO \l |EMPNO \l ENAME \l JOB \l MGR \l STARTDATE \l SAL \l COMM \l " shape = "record" ];


  "SCOTT.DEPT":"PK_DEPT"->"SCOTT.EMP":"FK_DEPTNO" [arrowhead = crow];
}

Which after being put through GraphViz

$ dot -Tsvg scottsimple.gv -o scottsimple.svg

gives us a nice image of the relationship. The table name is prefixed with the schema to make sure that you can identify the table correctly for those cases where the same table exists in multiple schemas, and the columns involved in the relationships are highlighted in their own box. The non-relationship columns appear at the bottom of the symbol (column ordering is maintained and hidden columns are not shown).

scottsimple.svg

For a more interactive experience run the SVG file through this converter, svgconverter.rb which converts the SVG file to a HTML file with some D3JS JavaScript that highlights relationships and tables as you click on them - I found that this made it easier to trace through a model when there are 80+ tables.

$ ruby svgconverter.rb scottsimple.svg scottsimple.html

The resulting file when uploaded to a webserver that has d3.js in the right place is interactive - see scottsimple.html unlike the image above it can be clicked on to highlight the symbols or relationships - only the outer line of the table is clickable - the rest is left as an exercise for the reader.

Back to the future (again)

Posted by Pete McBreen Thu, 03 Dec 2015 04:27:00 GMT

Delving into the archives, Leslie Lamport wrote about the Future of Computing back in 2003. There are still too many programs that are not understood fully by their developers or maintainers.

Firefox is going to lose a lot of developers (and users)

Posted by Pete McBreen Thu, 26 Nov 2015 03:01:00 GMT

Somehow or other the Firefox community has convinced itself that scanning add-ons for vulnerabilities and malware is a good idea. Luckily Dan Stillman the developer of Zotero called them out on it pointing out that it is just Security Theater.

Firefox has always had lots of really large extensions, but by deciding that they must be signed and reviewed, the Firefox community has just committed itself to a LOT of extra work reviewing the extensions. Hence the dumb idea of scanning to see if there is anything malicious in it. Now that is an arms race that is going to be lost. The guys in the AdBlock game know that, a continual game of whack a mole. Actively developed extensions like Zotero really lose out because a manual review of a large codebase takes a long time, and scanning is insufficient (as the above link describes, it is easy to create an add-on that passes scanning and does nasty things).

Breaking news...programmers are not engineers

Posted by Pete McBreen Sat, 07 Nov 2015 23:59:00 GMT

The Atlantic just realized that programmers are not engineers

Sorry to break it to them, but this has been a topic of conversation long before I wrote the book Software Craftsmanship, which was published nearly 15 years ago.

I had forgotten about the Coding Horror website

Posted by Pete McBreen Thu, 30 Jul 2015 20:28:00 GMT

And then this article about testing — Doing Terrible Things To Your Code reminded me to look at it again.

QA Engineer walks into a bar. Orders a beer. Orders 0 beers. Orders 999999999 beers. Orders a lizard. Orders -1 beers. Orders a sfdeljknesv.

I sure wish more programmers would focus a lot of attention on testing their own code before passing it on to QA/Test. That way the QA/Test team can focus on finding the requirements and interaction defects, rather than the simple coding mistakes that are often the bane of their existence

So much universe, and so little time

Posted by Pete McBreen Fri, 13 Mar 2015 02:52:00 GMT

AT LAST, SIR TERRY, WE MUST WALK TOGETHER.

Some quotes from Terry Pratchett

  • The whole of life is just like watching a film. Only it’s as though you always get in ten minutes after the big picture has started, and no-one will tell you the plot, so you have to work it out all yourself from the clues.
  • The presence of those seeking the truth is infinitely to be preferred to the presence of those who think they’ve found it.
  • It’s still magic even if you know how it’s done.
  • There are times in life when people must know when not to let go. Balloons are designed to teach small children this.
  • YOU HAVE TO START OUT LEARNING TO BELIEVE THE LITTLE LIES.
  • The truth may be out there, but the lies are inside your head.
  • Goodness is about what you do. Not who you pray to.
  • I have no use for people who have learned the limits of the possible.

Kevlin Henney - Seven Ineffective Coding Habits

Posted by Pete McBreen Mon, 16 Feb 2015 02:49:00 GMT

Kevlin Henney - of Curly Bracket Languages fame has a good video of his presentation at a recent NDC conference Seven Ineffective Coding Habits of Many Programmers. As usual a very entertaining talk, but Kevlin is also spot on in identifying ways in which we are lead to make incorrect decisions about the code we are writing.

In it he references a paper from Rob Pike Notes on Programming in C. Although Rob Pike wrote that paper back in 1989 it is still relevant, as can be seen by his words about variable names:

Length is not a virtue in a name; clarity of expression is.

Crafstmanship in other fields

Posted by Pete McBreen Sun, 08 Feb 2015 21:15:00 GMT

Interesting long article on a Kitchen Bladesmith, describing the expertise that goes into making kitchen knives, especially those for the chef market.

The Hard Way is Easier...

Posted by Pete McBreen Thu, 20 Nov 2014 05:00:00 GMT

Zed Shaw has written an introduction to his Hard Way series and in it he writes

The one skill that separates bad programmers from good programmers is attention to detail. In fact, it’s what separates the good from the bad in any profession. Without paying attention to the tiniest details of your work, you will miss key elements of what you create. In programming, this is how you end up with bugs and difficult-to-use systems.

A different take on software engineering

Posted by Pete McBreen Tue, 11 Nov 2014 17:25:00 GMT

In The Leprechauns of Software Engineering Laurent Bossavit has an interesting take on the folklore of software development.

A different view on testing

Posted by Pete McBreen Mon, 29 Sep 2014 02:16:00 GMT

The Beauty of Testing article has a different take on testing;

Test, to put it simply, is the conscience of a product.

Understanding the problem

Posted by Pete McBreen Wed, 02 Jul 2014 02:52:00 GMT

Software development is a hard problem.

Books like The Mythical Man Month, Set Phasers on Stun and The Inmates are running the Asylum have all pointed out in their own way that creating software is hard. Fred Brooks focused on the problem of large complex projects, and the problems that face project managers, the other two remind us that even small projects can fail because we still are not able to create software that is both easy to use and powerful enough to do that tasks that we want to do with software.

Until we are able to understand why software development is such a hard problem, we are not going to make much beyond incremental improvement. There will always be a few projects that through the operation of blind luck across millions of projects that results in seemingly reproducible improvement, but the normal regression to the mean will correct that eventually.

Craftsmanship around the web

Posted by Pete McBreen Mon, 30 Jun 2014 03:17:00 GMT

I didn’t see this when it was first written, but it matches with my recent experiences.

… most programmers simply don’t know where the quality bar is. They don’t know what disciplines they should adopt. They don’t know the difference between good and bad code. And, most importantly, they have not learned that writing good clean code in a disciplined manner is the fastest and best way get the job done well. – Robert Martin

Software Craftsmanship depends on developers knowing the craft of coding

Posted by Pete McBreen Thu, 05 Jun 2014 02:47:00 GMT

Many software developers do not seem to understand the basics of our craft. Recently I’ve seen

  • SQL queries that were massively more complex than they needed to be - that when simplified, without any database changes ran more than 10 times faster
  • Client server applications that issue nearly 1000 SQL queries while refreshing what is supposed to be an interactive screen - the end result being that the poor user has to wait 5 to 10 seconds for the screen to refresh after conceptually simple actions
  • Supposedly secure web applications that sent Active Directory usernames and passwords in cleartext across HTTP connections
  • Code that created connections to external resources but forgot to free them - made for a very effective rate limiting mechanism since the external resource freed unused handles about an hour after they were last used

There have been lots more examples, but most of them fall into the category of being unbelievable if you were not a direct witness to the utter ignorance of the basics of software development that brought them to my attention.

Maybe it is time that we started to focus on the basics of the craft of coding before we get too far into creating overly complex systems that nobody can understand or fix.

We can learn a lot from Healthcare.gov...

Posted by Pete McBreen Fri, 22 Nov 2013 04:10:00 GMT

But I’m not sure we will learn the right lessons.

In the early 1980’s I worked on applications that had to process 300 transactions per minute. At the time that was considered a heavy load for a Dec Vax to deal with. By the late 1980’s the same applications were dealing with 1,000 transactions per minute because the hardware had got a lot faster. In the mid-1990’s I worked on a small scale credit card processing application, running on a later incarnation of the Dec Vax that was able to handle nearly 20 transactions per second. In the late 1990’s I worked on a stock exchange system that had to deal with what we thought at the time was a stupidly large number of messages per second … little did I know. Fast forward to the early 2000’s and I worked on consumer facing web applications that had to withstand 10,000 requests per second. By 2010 I had the fun of being on a real web scale project, experiencing the joys of being linked to by Digg and CNN and hoping that the ensuing millions of requests per hour would not being the system down.

All this is to say that dealing with internet scale applications is a solved problem, but it seems that whoever was involved in the Healthcare.gov fiasco did not realize that. Dave Winer pointed out that the Government develops software differently, but there is no excuse for building a site that cannot handle the traffic.

I disagree with Bob Goodwin - there is no software engineering crisis - OK I have to say that because I wrote the Software Craftsmanship book. But that is not the real reason I have to disagree - I have to disagree because whoever built the site went about it the wrong way. Dave Winer parodied the approach that big consulting companies take

They’d fully specify the software, the user interface, its internal workings, file formats, even write the user documentation, before a single line of code was written. Then they’d hand the parts off to development teams who would independently of each other create the components. Another team would do the integration.

The sad fact is that the big corporations that are awarded these big government contracts do not have a clue how to build web scale applications that work. They over promise and massively underdeliver. All too often large companies are awarded contracts to build large systems and fail to deliver anything of value except to their own shareholders.