Category: software development

dbMaintain

Since reading about database refactoring some while ago I’ve been seeking to adopt a tool to support automated tracking and application of database changes.  The best known solution for this is LiquiBase (fka Sundog Refactoring Tool), with over 10,000 downloads of LiquiBase Core recorded on SourceForge as I write this.  In order to adopt LiquiBase on an existing project, however, I was faced with replacing dozens of SQL DDL scripts with LiquiBase specific XML files.  There is a generateChangeLog command, but it has limitations: for example it does not recognize the primary key of Oracle index organized tables, and the output is not arranged anything like the documented best practices.

Instead, for now, I have adopted a newer tool called dbMaintain, which was presented at a Belgian Java User Group last April (see below) and a ServerSide news article last July.  dbMaintain provides similar functionality to LiquiBase, but instead of XML it simply passes through native SQL scripts using JDBC, making it much easier to adopt with confidence on an existing project.  I did run into one known issue with setting the default schema, but I’m happy to say Tim Ducheyne responded to my emails and provided a fixed snapshot within days.  I hope this project gains momentum, and I’m looking forward to the next release.

Unit testing: unitils & dbmaintain

Technorati Tags:

PS. Other less mature alternatives include SQL based Thoughtworks dbdeploy and Carbon Five’s c5-db-migration.  Another custom XML based solution is MIGRATEdb.  Sony Pictures Imageworks have ported ActiveRecord migrations to Scala and there is a relatively inactive port of ActiveRecord migrations to Groovy called Bering.

Oracle SQLDeveloper 2.1 vs Quest TOAD

This past week I’ve been seeing whether I can wean myself off TOAD to the new SQLDeveloper 2.1 that was officially released last month. SQLDeveloper is a Java based IDE and so, unlike TOAD, it can be used on OS X and Ubuntu. SQLDeveloper is not open source, but it is licenced for free, whereas TOAD costs almost a thousand dollars per developer for the entry level version.

Version 2.1 of SQLDeveloper shows considerable improvement compared to the original release, but there are still some rough edges. TOAD presents database connections (connection configuration) and sessions (active connections) clearly and separately, with schema browsers and worksheets for each session in separate windows, and highlighting of windows that share the same session. SQLDeveloper has a single connection navigator, perhaps more familiar to users of other IDEs, but each connection may have only one session: you have to duplicate and rename a connection if you want to establish another session using the same connection parameters. Working with TOAD there is nearly always a default current session context, so it was annoying with SQLDeveloper reports to be prompted repeatedly for the connection to use to run a report.

One of the features of SQLDeveloper that first interested me was Subversion support. Again, this is similar to other Java IDEs. I’ve used Team Coding with TOAD in the past but the integration with CVS or PVCS was not as seamless as one would like. Now I just use TortoiseSVN and make sure I open the local source file whenever I want to edit a stored procedure and never edit the stored procedure source in the database directly. Oracle provides a tutorial on Subversion with SQLDeveloper but in my opinion it completely misses the point by failing to explain how to maintain stored procedures.

In general TOAD still appears a little more polished: for example favorites support in the File Open dialog, and a popup calendar control for setting date bind variables when executing a query. TOAD provides better feedback: for example it automatically displays the row count in a status line for every worksheet query, but with SQLDeveloper you have to select a popup menu option. For long running procedure execs TOAD provides a script runner with animated graphics and a timer, but the default behavior for SQLDeveloper is to exec the procedure like sqlplus in a small log window that is not immediately obvious. On the other hand the dockable tabs of SQLDeveloper are easier to manage than the more dated MDI design of TOAD.

Perhaps the biggest surprise, however, was when I clicked Tools/Monitor SQL and got ‘The use of this feature requires that the Oracle Tuning Pack be licensed.’ Oracle is not giving everything away for free after all, and it looks like for more advanced features I may still be going back to TOAD for a little while.

Incanto

After months of delay I’ve finally switched all my database creation scripts to Incanto. The most obvious benefit is that my Ant build scripts are now more portable, as I am no longer exec’ing a shell script wrapper to sqlplus, and I no longer need Cygwin to run the build scripts on my Windows workstation. One of the nice features about Incanto is that it allows you to pass in an Ant propertyset which is used to define SQL*Plus variables for the invoked scripts. I find this useful for overriding schema names and allowing multiple sets of application schemas to be created in the same developer database, for example when researching support issues alongside new development. The Incanto website has a page of best practices including a <macrodef> which I placed in a shared build file and <import>ed into my various Ant scripts.

Incanto requires sqlplus to be on the PATH, so I handle that and setting login parameters in .antrc. One strange issue I encountered on SLES while making this transition was that even although sqlplus was on my PATH and executable from my shell scripts, when I used Incanto I got

java.io.IOException: java.io.IOException: sqlplus: not found

Changing the permissions on sqlplus from 750 to 755 resolved the issue, even though the executable already belonged to a secondary group of which I was a member. Note that if you don’t need the advanced features of sqlplus you can still execute PL/SQL with Ant’s sql task.

Subversion on Ubuntu – Multiple Repositories

Eighteen months have passed since I setup Subversion on Ubuntu and I’ve found multiple repositories are valuable for providing finer grained access control and easier storage management. Here is a revised incantation:

su -
adduser --system --home /srv/svnrepos --gecos "System account to run svnserve" svn
apt-get install subversion xinetd
cat >> /etc/xinetd.d/svn << "EOF"
service svn
{
        port                    = 3690
        socket_type             = stream
        protocol                = tcp
        wait                    = no
        user                    = svn
        server                  = /usr/bin/svnserve
        server_args             = -i -r /srv/svnrepos
}
EOF
/etc/init.d/xinetd restart

# for each repository, eg. myrepo1
$repo=myrepo1
svnadmin create /srv/svnrepos/$repo
chown -R svn:nogroup /srv/svnrepos

# uncomment line to use default password file
vi ~svn/$repo/conf/svnserve.conf

cat >> ~svn/$repo/conf/passwd << "EOF"
[users]
fred = *****
...

Google Trends – Databases & Languages

Google Trends is a great tool for graphing search term popularity over time. Here is a comparison of searches for several makes of database.

ScreenShot001 

Perhaps it’s a quirk of the phrases I used, but I was surprised to see Oracle so dominant, and even MySQL more popular than SQL Server.  PostgreSQL languishes in obscurity compared to MySQL, and Ingres barely registers at all.

Here’s some languages that I’m interested in.  Java is hugely dominant.

ScreenShot003

Let’s take out Java and zoom in.  I was surprised to see Ruby’s recent decline relative to Python.  Books on Scala have come out only recently.

ScreenShot001

Looking more specifically at JVM language dialects I’m surprised to see Jython competing so strongly with JRuby.  I’m not sure if I picked the right phrase for Groovy.

ScreenShot002

Choosing tools solely on the basis of popularity is obviously not a great idea, but I do feel better now about continuing to use Oracle, Java and Python.

P.S. See also the TIOBE Programming Community Index

cx_Oracle callfunc()

I like wrapping database queries in stored procedures: it keeps the SQL in the database, where Oracle can track its dependencies and flag if some change makes it invalid.  Here’s an example using Oracle’s demo schema SCOTT:

CREATE OR REPLACE PACKAGE emp_extract_k
AS
   TYPE emp_t
   IS
      RECORD( ename  varchar2( 10 ), hiredate date, sal     number( 7, 2 ) );
   TYPE emp_csr_t IS REF CURSOR
      RETURN emp_t;
   FUNCTION get_emps
      RETURN emp_csr_t;
END;
/
CREATE OR REPLACE PACKAGE BODY emp_extract_k
AS
   FUNCTION get_emps
      RETURN emp_csr_t
   IS
      c   emp_csr_t;
   BEGIN
      OPEN c FOR SELECT   ename, hiredate, sal FROM scott.emp;
      RETURN c;
   END;
END;/

Now suppose I want to take the output from a stored procedure like the one above and write it to a CSV file.  In Python this is trivially easy:

 import cx_Oracle
 import csv
 reportName = 'emp_extract_k.get_emps'
 connection = cx_Oracle.connect(userid)
 cursor = connection.cursor()
 rs = cursor.callfunc(reportName, cx_Oracle.CURSOR)
 fd = open(reportName+'.csv', 'w')
 csvFile = csv.writer(fd)
 header = []
 [header.append(col_defn[0]) for col_defn in rs.description]
 csvFile.writerow(header)
 for r in rs:
  csvFile.writerow(r)
 fd.close()

VBScript will never die, just fade away

I was looking for an official announcement of the end of life of VBScript but could not find one.  Instead I stumbled across the following MSDN blog post with a vague reference: "I believe that it was [at the] IT Forum in Nov 2005 that this was announced."  A colorful blog post from Ryan Stemkoski made me laugh, the comments are interesting also.  The closest I could find to an official Microsoft announcement was a blog post five years ago by Eric Lippert, who admitted to being the last person to ever add a feature to VBScript, in November 2000.

Adding a surrogate key to an Oracle table

This works for me for a mid-sized table.

ALTER TABLE foo ADD ( foo_id integer )
/
CREATE SEQUENCE foo_id_seq
/
CREATE OR REPLACE TRIGGER before_update_foo
   BEFORE INSERT OR UPDATE
   ON FOO
   REFERENCING OLD AS old NEW AS new
   FOR EACH ROW
DECLARE
   l_foo_id   foo.foo_id%TYPE;
BEGIN
   IF :new.foo_id IS NULL
   THEN
      SELECT   foo_id_seq.nextval INTO l_foo_id FROM dual;

      :new.foo_id := l_foo_id;
   END IF;
END;
/
UPDATE   foo
   SET   foo_id = NULL
/
COMMIT
/
CREATE UNIQUE INDEX foo_ak
   ON foo( foo_id )
/
ALTER TABLE foo MODIFY ( foo_id NOT NULL )
/
ALTER TABLE foo ADD (CONSTRAINT foo_ak UNIQUE (foo_id))
/

SDWest 2009

Monday. Disappointed to discover the coding dojo cancelled. Guessing that not enough people knew what it was and were not prepared to sign-up beforehand. Sat in instead on Scott Meyers’ ‘Better Software – No Matter What’: familiar material by a good presenter. Sofware quality depends on programmers, and how they exercise their discretion in implementing requirements: providing guidelines makes a significant difference. Insist on useful requirements. Create interfaces that are easy to use correctly and difficult to use incorrectly. Use design by contract to specify requirements using pre-conditions, post-conditions and invariants. Embrace static analysis. Favor constructor parameters over singletons.

Lunchtime presentation by Robert Martin, giving some inside history on Agile and Scrum, and how that has led to today’s movement for software craftsmanship.

Afternoon session on easyb and Groovy with Rod Coffin and Andy Glover: slow start before plunging into use of the requirements domain specific language with IntelliJ and Eclipse. Tested and documented version of a LIFO stack implemented with remarkable speed.

Evening discussion panel on SOA: a marketing buzz phrase past its prime. SOAP/HTTP web services remain the most robust solution even though simpler RESTful solutions have gained popularity on more ‘primitive’ platforms.

Finally an informal presentation by an engineering director and architect from LinkedIn describing their infrastructure and development practices. Tomcat, a custom MVC framework and JSP compiler, SpringRPC (~100 services), Jetty and Oracle on SPARC. Four million visitors a day peaking at 200,000 concurrent users. Originally pair programming but now simply at least two developers per project. No contractors, all 100 engineering staff in the same building. High end Mac workstations and laptops with Eclipse/IntelliJ. 1,500 JUnit/HttpUnit test cases for ~1,000 KLOC automated using Hudson. Database partitioned both vertically (by functional area) and horizontally (by member id range). Back to the hotel past 9pm.

Tuesday. Morning session Dean Warner on programming in Scala, a scary-powerful collision of Java and Lisp/Erlang used by Twitter for higly parallel message handling. Afternoon session with Chris Richardson, author of ‘POJOs in Action’ giving a technical introduction to Spring Framework dependency injection (replacing singletons with constructor arguments) enabling Spring AOP for service methods. Also Spring JDBC and Hibernate, all using either XML or annotations.

Wednesday. After two days of half-day tutorials the conference format switching to four 90 minute workshops per day plus lunchtime presentations. Website navigation using menus and search, especially for e-commerce. Overlay menus at Amazon. Everyone familiar with the layout of Outlook. BabyNameWizard.com making novel use of AJAX. Neal Ford with an introduction to Ruby and JRuby, an interpreted language well suited to metaprogramming such as building test frameworks including Mocha. Robert Martin walking through some of his code from FitNesse: functions should be well named, short, and do one thing only. Two presentations from Scott Ambler on Agile development: delivering code early and often, using TDD, involving stakeholders, building self-organizing teams and delivering what the customer wants. Geographically dispersed teams with a 15% higher rate of project failure: fly people around or pay more in hidden costs. Evening awards ceremony: Dr. Dobbs Excellence In Programming award going to Scott Meyers and over a dozen Jolt awards made in surprising haste, including easyb. Reading the first chapters of ‘Groovy In Action’.

Thursday. Morning sessions on how to select a web framework and testing web applications. Grails immature and questions still over the Groovy classloader. GWT-Ext and Tapestry 5 or Wicket a recommended configuration. Rod demonstrating unit and component testing tools, including infinitest, WicketTester, HtmlUnit and Watij. Lunchtime presentation from Juval Lowy on the coming boom in power management software for homes, electric vehicles and buildings. Afternoon sessions on Guice, a lightweight dependency injection framework, and using the new javax.script package in Java 6.

Friday. Morning sessions on Spring 3.0 and Continuous Testing. Spring Framework clearly mature and featureful. Rod demoing intellitest IDE plugin allowing all dependent unit tests in a project to be run automatically whenever class bytecode changed. Afternoon sessions on xUnit testing and generating documentation during the build cycle. Dependency injection does not require a framework for in-house applications. Creating and maintaining mock objects for database services may be impractical, consider the Dependent Object Framework and possibly H2 instead. Demos of Hudson and Doxygen rounding out the conference.

What did I get from the conference? Most inspriring was seeing Rod demonstrating TDD in action with a hands on pair programming session and discussions of the supporting tools. Scott Meyers, Robert Martin and Scott Ambler reinforcing the importance of TDD in the bigger picture, Justin Gordon describing use of TDD on a significant IBM project. Automated testing driving the adoption of dependency injection frameworks such as Spring and Guice. A second significant theme was scripting languages – including their use for testing – with sessions on Groovy/easyb, JRuby/Mocha and Scala. Finally there were the demos and pointers to projects like GWT-Ext, Hudson and Doxygen worthy of further investigation.

facelets-tutorial javax/el/ELResolverClass

ICEfaces have published a facelets-tutorial

Deployment instructions were limited so I downloaded and unzipped the source file into the samples folder provided with ICEfaces-1.7.2-SP1.

ant help
ant clean
ant tomcat6.0

I then copied dist/facelets-tutorial.war to tomcat-6.0/webapps… localhost…log showed:

Feb 21, 2009 11:11:07 AM org.apache.catalina.core.StandardContext listenerStart
SEVERE: Exception sending context initialized event to listener instance of class com.sun.faces.config.ConfigureListener
java.lang.LinkageError: loader constraints violated when linking javax/el/ELResolver class
 at com.sun.faces.config.ConfigureListener.registerELResolverAndListenerWithJsp(ConfigureListener.java:581)

Incompatibility between the EL included with ICEfaces and the EL provided with Tomcat 6? Stopping Tomcat, removing webapps/facelets-tutorial/web/WEB-INF/lib/el-api.jar and restarting provides a quick fix. Closer inspection of the facelets-tutorial build.xml reveals it has entries obsoleted by the newer version of samples/etc/build-common.xml and so is still including jars not needed or desired with Tomcat 6.

Posts navigation

1 2 3 4 5 6
Scroll to top