For one thing, SQL Server provides the ability to send emails from within the database. I would have thought that this alone justifies better formatting than is currently available.

Secondly, we don’t live in a perfect world where we have the choice to put all code in the correct layer every time.  We might only have control of the data tier, with a presentation tier displaying everything returned from a stored procedure, and we may not be able to change the code in that presentation tier.

And finally, if date formatting should never be done in the database, why bother providing the CONVERT function at all? To provide it, but with only a very limited set of formats, seems inconsistent.

So one of the first thing I did when I came to SQL Server from Oracle was to code up a quick and simple date formatting routine, that I wanted to work in a similar way to Oracle’s TO_CHAR function for dates. I only included the most basic format support and expected to have to extend it regularly as my requirements got more complex, but in fact I don’t think I’ve done much in the way if altering it, it pretty much has met my requirements whenever I’ve needed it. I still use CONVERT when one of the Microsoft supplied date formats is acceptable, but this allows me a bit more flexibility. Being a user defined function, performance will be affected, so I use it sparingly, but it definitely comes in handy. It’s designed to accept a date, and then an Oracle-style format, like ‘DD MON YY HH:MI’, and it returns the input date in the specified format, as a string. Alternatively I allow the passing of special strings like ‘Standard Date’, ‘Time only’ etc, which can be customised for the requirements of a particular project.

As it was almost the first UDF I wrote, it doesn’t use best coding practices, and I can now see several improvements I could make, but for the few times I need it, this works fine for me. If it’s useful for you, feel free to copy it, and maybe make some of those long awaited improvements I never got round to making.

Dean

=====================================================================================
-- Author: D Bullen
-- Description: This procedure accepts a date value, formats it, and returns it as a string.
-- The format is passed in as the @Format parameter, and it accepts the following:
-- DD - Date
-- MM - Month number
-- MNTH - Month Name
-- MON - Month Short Name
-- YYYY - 4 digit year
-- YY - 2 digit year
-- HH - Hours
-- MI - Minutes
--
-- Alternatively prefined standard formats can be passed in and translated into basic
-- format types, e.g. 'Standard Date & Time' - translated to 'DD MNTH YYYY at HH:MI'
-- =====================================================================================
CREATE FUNCTION [General].[FormattedDate]
( @DateIn DATETIME
,@Format VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FormattedDate VARCHAR(100)

IF @Format = 'Standard Date' SET @Format = 'DD MNTH YYYY'
IF @Format = 'Standard Date & Time' SET @Format = 'DD MNTH YYYY at HH:MI'
IF @Format = 'Short Date' SET @Format = 'DD MON YY'
IF @Format = 'Short Date & Time' SET @Format = 'DD MON YY HH:MI'
IF @Format = 'Time Only' SET @Format = 'HH:MI'

DECLARE @DateNum VARCHAR(2)
SET @DateNum = CASE WHEN LEN(CAST(DATEPART(day, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(day, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(day, @DateIn) AS VARCHAR) END
DECLARE @MonthNum VARCHAR(2)
SET @MonthNum = CASE WHEN LEN(CAST(DATEPART(month, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(month, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(month, @DateIn) AS VARCHAR) END
DECLARE @MonthName VARCHAR(20)
SET @MonthName = DATENAME(month, @DateIn)
DECLARE @MonthShortName VARCHAR(3)
SET @MonthShortName = SUBSTRING(@MonthName, 1, 3)
DECLARE @YearName VARCHAR(4)
SET @YearName = DATENAME(year, @DateIn)
DECLARE @YearShortName VARCHAR(2)
SET @YearShortName = SUBSTRING(@YearName, 3, 2)
DECLARE @Hours VARCHAR(2)
SET @Hours = CASE WHEN LEN(CAST(DATEPART(hour, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(hour, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(hour, @DateIn) AS VARCHAR) END
DECLARE @Minutes VARCHAR(2)
SET @Minutes = CASE WHEN LEN(CAST(DATEPART(minute, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(minute, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(minute, @DateIn) AS VARCHAR) END

SET @FormattedDate = @Format
SET @FormattedDate = REPLACE(@FormattedDate, 'DD', @DateNum)
SET @FormattedDate = REPLACE(@FormattedDate, 'MM', @MonthNum)
SET @FormattedDate = REPLACE(@FormattedDate, 'MNTH', @MonthName)
SET @FormattedDate = REPLACE(@FormattedDate, 'MON', @MonthShortName)
SET @FormattedDate = REPLACE(@FormattedDate, 'YYYY', @YearName)
SET @FormattedDate = REPLACE(@FormattedDate, 'YY', @YearShortName)
SET @FormattedDate = REPLACE(@FormattedDate, 'HH', @Hours)
SET @FormattedDate = REPLACE(@FormattedDate, 'MI', @Minutes)

IF @Format = 'Standard Date & Time' AND @FormattedDate LIKE '%at 00:00' SET @FormattedDate = REPLACE(@FormattedDate, 'at 00:00', '')

RETURN @FormattedDate

END


About the poster

Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.

First, run the command

$ touch /tmp/now

All we’re doing here is to create a file with a timestamp of “right now”.

Now fire up your graphical config tool and make whatever change you want. Immediately afterwards, run the command:

$ find /etc -newer /tmp/now 2> /dev/null

This will show you all the files under the /etc directory (where most system configuration files live) that have been modified since you ran the ‘touch’ command. (My assumption is that this is system-wide configuration you were changing, not a per-user setting. All per-user settings, of course, are stored in your home directory, not under /etc.)

The technique isn’t foolproof — you may get some false positives (files that changed for some other reason), but it’s a pretty good guide.

Here’s an example — on Ubuntu I used the System -> Administration -> Login Window tool to change the theme used by the login screen. After this, the find command produced output like this:


$ find /etc -newer /tmp/now 2> /dev/null
/etc/gdm
/etc/gdm/gdm.conf-custom


Now that we know which file got changed, we can go a step further and investigate which entries in the file were altered. Begin by making a copy of the current configuration:

$ cp /etc/gdm/gdm.conf-custom /tmp

Now run the Login Window configuration tool again and make another change. Afterwards, display the differences between the original configuration file and the new one:


$ diff /etc/gdm/gdm.conf-custom /tmp/gdm.conf-custom
79,82c79
< GraphicalTheme=circles
---
> GraphicalTheme=HumanCircle


Now, you can see exactly which lines within the config file were changed.

About the poster

Dr Chris Brown is Impartica’s Linux course leader and has been using UNIX for over 30 years. He is author of the book “UNIX Distributed Programming” published by Prentice Hall, and of “SUSE Linux” published by O’Reilly. He also writes a regular column for the UK magazine “Linux Format”.

Problem

Specifically, he had a table which held details of stocks traded. For simplicity, let’s just image it has two columns – amount_sold and date_sold. Such a table might look like this:

SELECT amount_sold, date_sold FROM stock_sales 

amount_sold  date_sold
-----------  -----------------------
1700         2010-02-15 00:59:49.190
1900         2010-02-16 05:47:49.203
1400         2010-02-24 20:11:45.873

The table he needed to join to held exchange rate values at different points in time. Again stripped down to the columns relevant to our problem, we could imagine it would look something like this:

SELECT rate, effective_date FROM exchange_rate 

rate      effective_date
--------- -----------------------
1.09      2010-01-01 00:00:00.000
1.12      2010-02-17 11:14:00.000
1.2       2010-02-20 13:24:00.000

What he wanted to do – as you can probably already anticipate – is to find the appropriate exchange rate for each sale, in other words to join the stock_sales table to exchange_rate, where date_sold is greater than or equal to effective_date, but less than effective_date in the next exchange_rate record.

Great problem! In 12 years I find it difficult to believe I haven’t come across this type of problem before, but if I have I can’t remember it! So it gave me some thinking to do.

Here is my answer to the problem. Please let me know if you can find a better one!

My solution

My immediate thought was that if the exchange_rate table was in a format that held “effective from” and “effective to” columns, then the solution would be easy to achieve with a standard non-equijoin. So if it looked like this:

rate    effective_date_from     effective_date_to
------- ----------------------- -----------------------
1.09    2010-01-01 00:00:00.000 2010-02-17 11:14:00.000
1.12    2010-02-17 11:14:00.000 2010-02-20 13:24:00.000
1.2     2010-02-20 13:24:00.000 2010-02-27 14:44:49.830

Then this query would give him the data he needed.

SELECT amount_sold, date_sold, rate
FROM stock_sales
INNER JOIN exchange_rate
ON (stock_sales.date_sold >= exchange_rate.effective_date_from AND stock_sales.date_sold < exchange_rate.effective_date_to)

amount_sold date_sold               rate
----------- ----------------------- ------
1700        2010-02-15 00:59:49.190 1.09
1900        2010-02-16 05:47:49.203 1.09
1400        2010-02-24 20:11:45.873 1.2

So, if I could get my exchange_rate data into that format, then I could use an inline view to get my desired result. (I could also use a common table expression, and this would be slightly easier, but as these weren’t covered on the course I was delivering I preferred to stick to inline views). So how to get my data in the format I wanted?

I thought a good starting point would be to generate two copies of the exchange_rate table data. If I could then give each one an ID column, then I could join them together, matching each row in the first table to the following row in the second table. Then the effective_date column from the first table would be my “effective_date_to” column, and the same column from the second table would be “effective_date_from”. To generate the “ID” column for each, I used the ROW_NUMBER() function.

The query

SELECT ROW_NUMBER() OVER (ORDER BY effective_date) rownum, rate, effective_date FROM exchange_rate

gives me my first copy of the data, with a numeric “key” against each record, like so:

rownum   rate    effective_date
-------- ------- -----------------------
1        1.09    2010-01-01 00:00:00.000
2        1.12    2010-02-17 11:14:00.000
3        1.2     2010-02-20 13:24:00.000

I can therefore slightly adapt that, subtracting 1 from the generated number, to provide a second copy of the data:

SELECT (ROW_NUMBER() OVER (ORDER BY effective_date))-1 rownum, rate, effective_date FROM exchange_rate 

rownum   rate    effective_date
-------- ------- -----------------------
0        1.09    2010-01-01 00:00:00.000
1        1.12    2010-02-17 11:14:00.000
2        1.2     2010-02-20 13:24:00.000

Each of these queries can therefore be made into inline views, and these can then be joined on the rownum column. This will join together records in the first result set with the next record on in the second. (If I was happy to use a common table expression I wouldn’t need to take this second step, I could have declared a CTE from the first query and used it twice.)

SELECT exchange_rate1.rate
,      exchange_rate1.effective_date AS effective_date_from
,      exchange_rate2.effective_date AS effective_date_to
FROM
  (  SELECT ROW_NUMBER() OVER (ORDER BY effective_date) rownum, rate, effective_date
     FROM exchange_rate) exchange_rate1
LEFT OUTER JOIN (SELECT (ROW_NUMBER() OVER (ORDER BY effective_date))-1 rownum, rate, effective_date
                 FROM   exchange_rate ) exchange_rate2
ON (exchange_rate1.rownum = exchange_rate2.rownum)

rate   effective_date_from     effective_date_to
------ ----------------------- -----------------------
1.09   2010-01-01 00:00:00.000 2010-02-17 11:14:00.000
1.12   2010-02-17 11:14:00.000 2010-02-20 13:24:00.000
1.2    2010-02-20 13:24:00.000 NULL

This is now pretty close to what we want. I just need to handle trades made since the last exchange_rate became effective – i.e the NULL that appears at the end of my result set. There are a couple of ways to do it, but for simplicity I will just use ISNULL to replace the null with any future date. Any future date is fine, as no future trades are likely to be recorded in my stock_sales table.

SELECT exchange_rate1.rate
,      exchange_rate1.effective_date AS effective_date_from
,      ISNULL(exchange_rate2.effective_date, DATEADD(day, 1, getDate())) effective_date_to
FROM
  (  SELECT ROW_NUMBER() OVER (ORDER BY effective_date) rownum, rate, effective_date
     FROM exchange_rate) exchange_rate1
LEFT OUTER JOIN (SELECT (ROW_NUMBER() OVER (ORDER BY effective_date))-1 rownum, rate, DATEADD(millisecond, -1, effective_date) effective_date
                 FROM   exchange_rate ) exchange_rate2
ON (exchange_rate1.rownum = exchange_rate2.rownum)

rate   effective_date_from     effective_date_to
------ ----------------------- -----------------------
1.09   2010-01-01 00:00:00.000 2010-02-17 11:14:00.000
1.12   2010-02-17 11:14:00.000 2010-02-20 13:24:00.000
1.2    2010-02-20 13:24:00.000 2010-02-26 15:38:29.493

Having got my data in to the format I was looking for, I can now join in my stock_sales table using a standard non-equijoin:

SELECT amount_sold, date_sold, rate FROM stock_sales
INNER JOIN
	(SELECT exchange_rate1.rate
	 ,      exchange_rate1.effective_date AS effective_date_from
	 ,      ISNULL(exchange_rate2.effective_date, DATEADD(minute, 1, getDate())) effective_date_to
	 FROM
	   (  SELECT ROW_NUMBER() OVER (ORDER BY effective_date) rownum, rate, effective_date
 		 FROM exchange_rate) exchange_rate1
 	 LEFT OUTER JOIN (SELECT (ROW_NUMBER() OVER (ORDER BY effective_date))-1 rownum, rate, DATEADD(millisecond, -1, effective_date) effective_date
					 FROM   exchange_rate ) exchange_rate2
	 ON (exchange_rate1.rownum = exchange_rate2.rownum)
	 ) exchange_rates_range
ON (stock_sales.date_sold >= exchange_rates_range.effective_date_from AND stock_sales.date_sold < exchange_rates_range.effective_date_to)

amount_sold date_sold               rate
----------- ----------------------- ----------------------
1700        2010-02-15 00:59:49.190 1.09
1900        2010-02-16 05:47:49.203 1.09
1400        2010-02-24 20:11:45.873 1.2

Conclusion

This was a nice little problem, and one which I’m sure will have many practical uses. I would be interested in hearing anyone else’s alternative ways to solve it.

About the poster

Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.

Can you give a potted history of your coding career?

1987

I started programming BASIC on an BBC Acorn Electron. Something like:

10 print "Kris is Cool "
20 goto 10

1989-1993

After this I remember getting a Commadore 64 and later an Amiga 500 although to be honest I mostly played games until I got an PC (486 with a Turbo button!) running MS-DOS. I picked up BASIC again, this time Microsoft’s QBasic. This was my first exposure to a debugger and the game Gorillas.

1995

Shortly after the move from DOS to Windows PC’s became more affordable and appeared in libraries, cafes and schools. I briefly dabbled with C before being introduced to Borland Delphi (Object Pascal) version 4. This was my first exposure to an Object Orientated language. Shortly afterwards I got some work programming desktop applications before finishing my A-Levels in 1998.

2000

Within the next few years the Internet became more affordable and I started developing simple static websites using Macromedia Dreamweaver and Fireworks for local businesses.

My natural progression was to pick up PHP and start developing dynamic websites. I did this for many years of web development using PHP like many people blissfully unaware of HTML/CSS standards and best practices in web development. By necessity I also started to get involved in Linux server administration such as installing packages and configuring Apache.

2004

It was only much later that blogs started to become popular due to open source projects such as WordPress and posts on separation of data (HTML), presentation (CSS) and behaviour (Javascript) caught my eye and a light bulb was illuminated.

This somehow led me to my most exciting discovery to date, the RubyOnRails framework.

I think the first exposure to RubyOnRails was a short video of pre-release Rails where someone, presumably David Heinemeier Hansson, generated a scaffold application which talked to the database and generated HTML allowing you to create, edit and delete records. Pretty basic functionality but what was impressive was it only took a few minutes to complete.

So now I’ve been working with Rails for around 5 years and have no doubts I made the right choice.

What was it about RubyOnRails that appealed you?

At the time I mostly hashed together PHP code, HTML and SQL with no real structure. The most abstraction I managed was putting the database connection code in a separate file. I was not aware of a better way to manage my code base so larger projects quickly became unmanageable. This was not a consequence of using PHP but of the standard practice within the PHP community. Because code was being developed in a ad-hoc manner it made working as part of a team near impossible as each developer had their own way of doing things, there were no conventions and no coding standards. This didn’t make very good business sense. Projects were costly in terms of time and estimation was difficult in the first place. This uncertainty never inspired much confidence from prospective clients. Because one person held the knowledge needed to fully understand the code base development needed to stop to get a new developer up to speed. Not only that a completely new team could not inherit the project months or years later when the original developer may have moved on.

RubyOnRails changed this, it is a framework. A solid foundation on which to build. Everything has its place. It is modular and works out of the box. I could pick up a Rails application, even a complex one, written by someone else and completely understand it without any supporting documentation in a relatively short amount of time.

Rails abstracts away all the mundane, repetitive jobs that are common to building web applications. This really appeals because not only is coding more enjoyable but you are much more productive. You could get something up and running really quickly. It allows you to concentrate on the unique and challenging aspects of a website. I can show my client a working prototype in a few days instead of a few weeks and respond to change early on in the project.

This was also my first exposure to the highly expressive Ruby language in which Rails is written.

Has selling Ruby/Rails to clients been difficult?

Businesses don’t tend to be interested in which technology you use to develop their website, they are more sensitive about budget. Using Rails enables you to outperform your competitors in terms of time/budget and bonds well with the values of Agile project management techniques such as delivering working software early and often, not only responding to changes in requirements but embracing them. It is the features of Rails which bring these benefits.

Also I’ve done a lot of sub-contractor work for web design companies. They tend to favour PHP based solutions because there is an abundance of PHP programmers. The market is saturated especially when you consider outsourcing overseas. Of course as mentioned earlier this can be a bit of a false economy for any sizeable project if you become restricted to one developer ‘teams’.

One of the web design companies I was working for had hired me to develop an ‘energy analysis’ application for a large aggregates company. We started using PHP and in the second year the code base started to take on a lot of technical debt due to convoluted hacks and bodge jobs which were employed to keep costs down. I had recently discovered Rails and had produced a few basic applications. I introduced the idea of using Rails but it was rejected and we continued on until three months later when I really could not go on. That week I rewrote the entire application in just a couple of weeks in Rails. We never looked back. From then on development proceeded at a much quicker pace and I could answer ‘yes’ to new features without having to worry about the code becoming unmaintainable.

What was the most difficult aspect of learning Rails?

At first I used Rails without really learning Ruby, the language in which it is written. In the beginning this wasn’t really a problem because Rails allows you to get up and running straight away with some simple configuration. But once you move past this point of using Rails as is and wanting to do some more advanced stuff that’s when you need to learn the Ruby language and become familiar with some of the objects in the core and standard libraries.

Ruby also has some features which are unique, such as blocks, which don’t seem to have a parallel in other languages.

Reading other peoples code was maybe the fastest way I leap frogged my understanding not only of Ruby but the “Ruby Way”. It’s hard to put your finger on exactly what the “Ruby Way” is. It’s kind of a collective best practice as defined by the community which you pick up over time.

Aside from Ruby and Rails what other software have you been excited about recently?

Definitely ‘Git’. It’s a distributed version control system which has become popular in the Rails community. It allows you to manage your source code individually or as part of a distributed team. Git is an open source project and was started by Linus Torvalds to manage the Linux Kernel code base.

What kind of projects have you been working on with Rails?

All the websites I code are now written in Rails. We recently delivered the Information Prescriptions project, which is an NHS funded knowledge base covering a wide range of health conditions such as Neurological, Long Term, Heart, Lung and Breathing Conditions. All information is categorised using demographics such as age, gender, and location. The project has been running over 2 years now and we are continually evolving the website to better meet the needs of the people using the site.

Last year we delivered a project, ‘Evolve’, for Nottingham Trent University which allowed students to be matched with charities looking for particular skill sets. It incorporated a lot of Social Media like features such as profiles, messaging, personal blogs and the ability for people to form groups with complementary skills.

Aside from this we have developed a variety of websites in the usual categories such as Online Learning, Customer Management, loads of Content Management Systems, E-commerce Solutions and Web Services.

I have also released a few open source projects for Ruby/Rails. I have written a series of automated tasks to help transparently incorporate non-technical graphic designer’s work in to our central code repository. This gives us a history of changes to HTML templates, images, CSS and Javascript files. This allows them to work with tools they are used to such as Dreamweaver and FTP. There are also a few experimental ideas on there including a Ruby chat server which keeps a HTTP connection to the browser open permanently allowing the server to push real time data to the browser.

Difficulty of deployment and scaling have been cited as failings of Rails, whats your take?

I think deployment was an issue in the very early days when the only option was to use FastCGI which was typically a pain to setup and far from reliable. Now days however we have a wealth of deployment options including standalone servers for Rails, an Apache module which allows you to just upload your code (ala PHP) and thanks to the JRuby project interfaces to Java Application Servers such as JBoss if you need to deploy in a Java only environment.

We also have a number of deployment automation tools which allow us to setup and deploy applications in seconds without having to even log in to the server(s).

The beauty of these tools is that you can start off with a super simple light weight setup and scale up at a later date if required. You can even have your application scale automatically up/down on demand if you are deploying to the cloud such as EC2.

It’s out of the scope of Rails itself but if you are managing your own server we also have tools such as Deprec and Chef which will provision a vanilla Linux server which an entire Rails stack including the database and web server.

Can you give some examples of high profile websites which are written in Rails?

Everybody’s heard of Twitter, right?

Yell, Scribd and Hulu are other examples of very high traffic sites. There are literally tens of thousands of sites out there both big and small which use Rails.

Twitter is known to have had issues scaling and this seems to have reflected badly on Rails…

To be honest scaling has never been an issue for websites written in Rails, since it scales in the same way as every other website, at the HTTP level. If you are lucky enough to attract enough traffic you start by caching as much as possible, optimising database calls and maybe moving your database on to a separate server. This is all configurable in Rails and standard practice for any website regardless of the technology.

The bottle neck for many high traffic websites is nearly always at the database tier. This was particularly pronounced for Twitter because it accepts real time content from any of its millions of users, peaking at 300 ‘tweets’ per second. I believe Twitter now keeps a lot of their data in memory and they wrote their own data storage to handle their unique case.

You are presenting the Ruby and a Rails courses for ourselves can give us an idea of what delegates will take away with them?

The courses can be taken individually however they run over consecutive days and are designed to complement each other. I have condensed years of experience into a few days so it’s pretty action packed. I’d like to inspire developers and show them things that will make them think ‘wow’. It’s important that delegates to go away with a practical and working knowledge so we use live coding sessions, participatory discussion and practical examples to collectively explore Ruby and Rails.

The 2 day Ruby course will be an introduction to many of the unique aspects of Ruby which make it really stand out as a general purpose language. It will not be Rails specific.

We will investigate the Ruby language, its object model and how to write solid object orientated code and tools such as package management, automated build tools and documentation which come packaged with Ruby things that really simplifies life as a developer.

We will go on to look at the ‘magical’ side of Ruby which allows the Rails framework and many of plugins and libraries to be as concise as they are. This will include a look at advanced topics such as meta-programming, DSL’s and code evaluation.

The Rails course which is spread over 3 days will start from the premise of never having used Rails before and we cover a lot of ground.

In the first day we will look at each of the core modules which make up Rails and how they are wired together allowing you to easily manage your data, render HTML and send emails. By developing a working application together I hope to demonstrate practical tips and techniques which I have accumulated over many years.

The second day will start with a focus on logins, permissions, security concerns and how these are handled in Rails. This will be followed up with a look at advanced concepts within each of the core modules.

The third day will looks at deployment options, debugging and optimisation, writing your own plugins and alternatives to the default Rails core modules.

Finally, where do you see the future for Rails?

Now a lot of initial hype has died down and we are seeing large projects being deployed in Rails with substantial user traffic which are scaling well it has silenced a lot of the unfounded doubt people might have had.

We are seeing healthy competition in the plugin arena in categories such as file upload, XML parsing, authorisation, authentication and the like. There is an abundance of high end well documented plugins.

Ruby 1.9 is just around the corner which looking at the benchmarks will give some great speed improvements. The Rails core team are working hard on gaining compatibility.

I don’t think the true potential of JRuby has been realised yet. JRuby is an alternative to the standard Ruby interpreter which allows you to run Ruby code on the JVM (Java Virtual Machine) and provides a two way communication between Ruby and Java. Allowing any Java library to be used from within Ruby. This includes as mentioned earlier running a Rails app inside something like Glassfish or JBoss.

Dr. Chris Brown has been using UNIX as a software developer and system administrator since its pioneering days over 30 years ago, and has used Linux professionally and at home for about 10 years. He has extensive experience in curriculum design, and in addition to Linux training he has written hands-on courses in UNIX/Linux system programming, network programming, PHP, and distributed computing, and has edited and provided instructional design support for many others.

In addition to his time as a Research Fellow at Sheffield University, Chris has had a “proper job” as a Product Manager for the training company Learning Tree International, which saw him living and working in Virginia, USA for a while.

Dr. Brown has taught UNIX and Linux extensively for more than 20 years, mostly in Europe and the USA but also in Canada, India, Hong Kong and Brazil. He provided in-depth technical training on SUSE Linux to Novell’s consultants and IT engineers. He developed training content for Canonical’s “Ubuntu Certified Professional” training and wrote their “Deploying Ubuntu Server” course, and was master trainer for their train-the-trainer program.

He is author of the book “UNIX Distributed Programming” published by Prentice Hall, and of “SUSE Linux” published by O’Reilly. He also writes a regular column for the UK magazine “Linux Format”.

He is an ardent supporter of Linux and the Open Source movement and brings an enthusiasm, experience, depth of knowledge, and humour to the classroom to make for an effective and enjoyable learning experience.

Chris holds a BA in theoretical physics, an MA, and a Ph.D. in particle physics, all from Cambridge University. He also has RedHat RHCE, Novell NCLP and Ubuntu UCP qualifications and is a Ubuntu
Certified Instructor.

Links

• Chris Brown on Amazon.com
• Read Chris’ column on Linux Format
• Book on Linux: A Comprehensive Introduction

Firstly, you can insert more than one row into a table using a single INSERT statement. Here is the syntax:

INSERT INTO monthly_sales (office_id, sales_year, sales_month, sales_value)
VALUES (42, '2009', '01', 324877)
, (42, '2009', '02', 329483)
, (42, '2009', '03', 381002)
, (89, '2009', '01', 50228)
, (89, '2009', '02', 49763)
, (89, '2009', '03', 49883)

Quite a neat addition, certainly very readable. Of more practical use possibly, the six rows to be inserted now either stand or fall together: if any of the value sets contain invalid data, the whole statement will fail and no rows will be inserted. In situations where this behaviour is desirable, this single statement can replace a potentially complex T-SQL block. (But of course beware that in some cases, you may NOT want each row to rely on others, and this could end up being a curse rather than a blessing).

The second use for row constructors is in formulating an on-the-fly virtual table, of the type you may previously have used a SELECT/UNION combination for.

Consider the following requirement. Using values from the table above, show the monthly sales for office 42 for every month in 2009. Since we only have data for the first three months, we will have to join the table to one we create on the fly. The following statement would probably be the best fit in previous versions of SQL Server:

SELECT ilv.month_name, ISNULL(ms.sales_value, 0) AS sales_value
FROM monthly_sales AS ms
RIGHT OUTER JOIN
( SELECT '01' AS month_name
UNION
SELECT '02' AS month_name
UNION
SELECT '03' AS month_name
UNION
SELECT '04' AS month_name
UNION
SELECT '05' AS month_name
UNION
SELECT '06' AS month_name
UNION
SELECT '07' AS month_name
UNION
SELECT '08' AS month_name
UNION
SELECT '09' AS month_name
UNION
SELECT '10' AS month_name
UNION
SELECT '11' AS month_name
UNION
SELECT '12' AS month_name) AS ilv ON (ilv.month_name = ms.sales_month AND ms.office_id = 42 AND ms.sales_year = '2009')
ORDER BY 1

Results:

01 324877
02 329483
03 381002
04 0
05 0
06 0
07 0
08 0
09 0
10 0
11 0
12 0

However the row constructor allows us to use a neater syntax to build up the inline view. Try this:

SELECT * FROM (VALUES ('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS ilv (month_name)

You will see it gives us the same result as the sequence of SELECTs and UNIONs in the main query, i.e. a single column, 12 row table, called ilv, with the column name of month_name. We can then join this into our monthly_sales table as follows:

SELECT ilv.month_name, ISNULL(ms.sales_value, 0) AS sales_value
FROM monthly_sales AS ms
RIGHT OUTER JOIN
( VALUES ('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS ilv (month_name)
ON (ilv.month_name = ms.sales_month AND ms.office_id = 42 AND ms.sales_year = '2009')
ORDER BY 1

Results:

01 324877
02 329483
03 381002
04 0
05 0
06 0
07 0
08 0
09 0
10 0
11 0
12 0

You are not restricted to one column, that’s just the example I chose to use.

Although it probably won’t make a major difference to your SQL life, keep row constructors in mind when working on SQL Server 2008.

About the poster

Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.

Not only does this have a cool name, it’s actually a cool feature.

Flashback was originally introduced in Oracle 9i, and improved in 10g. The feature I’m going to demonstrate today – the SELECT .. AS OF TIMESTAMP clause – is available in Oracle 10g Release 2 and higher.

Flashback allows you to view a table as it was at a previous point in time. Easily. Really easily, actually.

Rather than explain that any further I’ll show you the SQL and let you see for yourself. If you’ve never seen this feature before, I predict you’ll be impressed!

First I’ll set up my test table and data:

CREATE TABLE staff (full_name VARCHAR(20));
INSERT INTO staff (full_name) VALUES ('Susan Smith');
INSERT INTO staff (full_name) VALUES ('Harry Tomlinson');
INSERT INTO staff (full_name) VALUES ('Stuart Haines');
COMMIT;

SELECT * FROM staff;

FULL_NAME
--------------------
Susan Smith
Harry Tomlinson
Stuart Haines


Okay, nothing new so far. But what we’re going to do now is to change the data, commit the changes, but use Flashback SQL to go back and see the data as it is now.

To do this we’re going to use a timestamp, so let’s just capture the current timestamp:

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------------------------------
18-APR-09 10.37.31.765000 +01:00

Now we’ll change the data and commit the changes:

UPDATE staff SET full_name = 'Susan Brown' WHERE full_name = 'Susan Smith';
DELETE FROM staff WHERE full_name = 'Stuart Haines';
UPDATE staff SET full_name = UPPER(full_name);
COMMIT;

SELECT * FROM staff;

FULL_NAME
--------------------
SUSAN BROWN
HARRY TOMLINSON

So we’ve changed and committed the data. Now let’s test my claim. I said that Flashback would let us see the data as it was at a previous point in time, and that it would be really easy to do. So let’s try to get a view of the data as it was a few minutes ago, before we made those three changes.

It’s as simple as this:

SELECT * FROM staff AS OF TIMESTAMP TO_TIMESTAMP('18-APR-2009 10:37:31AM', 'DD-MON-YYYY HH:MI:SSAM');

FULL_NAME
--------------------
Susan Smith
Harry Tomlinson
Stuart Haines


So, all we’ve done is added ‘AS OF TIMESTAMP…’ to our SELECT statement, then used the TO_TIMESTAMP function to specify the time we want to ‘flash back’ to – the timestamp we retrieved just before we made the changes.

Those of you who grew up on earlier versions of Oracle, like me, or with other RDBMSs, I think will be impressed. The simplicity in particular makes it a really useful addition – no set-up needed, it’s right out of the box.

This feature is primarily designed for ‘undo’ functionality, allowing you to check or roll back unintended transactions. But you can probably think of production scenarios where you’d want to use it. You can create views based on Flashback SQL. Performance may not be particularly geared towards that type of usage though – check the documentation for the version of Oracle you are using and speak to your DBA about whether it makes sense to use it.

This is just one small feature of Oracle’s Flashback technology, there are other things you can do in the various RDBMS versions since Oracle 9i. Instead of the TIMESTAMP option you can specify the System Change Number (SCN) – an internal number Oracle uses to keep track of changes. Instead of using the AS OF clause on each SQL statement you can temporarily flash back a table – or even a whole database – to a point in time for the rest of the session. You can drop a table, and then flash it back to before the drop.

Hopefully this post has given you a flavour of what’s possible and will maybe encourage you to go and research it more yourself.

About the poster

Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.