# Stupid Oracle Tricks #1: SQL substitution variables

A common behaviour of a program, and therefore of my SQL script to replicate the program, is:
1) Retrieve some value from reference data
2) Use it repeatedly in SQL statements

If this reference data differs by environment or by day, this adds a manual step to the SQL script.

Here’s a neat trick to save the output of the reference data query and use it later:

column useful_value_alias new_value useful_value

select as useful_value_alias from where

select ‘Here is the useful value: ‘ || &useful_value from dual;

This sets up a column alias that stores the value for the column in a substitution variable named “useful_value”. To substitute the value in later SQL, we stick an ampersand in front of the variable name.

It beats hardcoding the value multiple times later in the SQL script, and it also beats using a nested select or join on the reference table over and over.

For lots more details on substitution variables, reference this blog post at OracleFAQ.

(To set the value explicitly, DEF variable_name=value)

I’m currently distracted from life, immersed in Concepts of Modern Mathematics.

Did you know that you can find out whether a number is prime by multiplying all the integers before it, adding one, and then dividing by the number of interest? Me neither! it’s like this:

`a number p is prime if and only if: (1*2*3*...*(p-1) + 1) mod p = 0`

So, if the product of all positive integers less than p is one short of dividing evenly by p, then p is prime. Otherwise, not prime.

As the book points out, this is not a practical way to determine primeness, because if p is 17, that product is fourteen digits long. However, there’s a sneaky way to make this more practical.

This formula came out of the concept of arithmetic modulus p, which is a strange sort of arithmetic that considers only the numbers 0 through p-1. Any higher or lower numbers circle around. Any number above p-1 reduces to its remainder when divided by p. It’s a bit like when a two-year old is counting: “One, two, three, four, one, two, three…”

Since this equation came out of arithmetic modulus p, we can use this property to shrink the product as we do the multiplication. While we multiply 1 times 2 times 3 times 4, etc, we can stop after each multiplication and mod p. That way the product never gets crazy high — at most (p-1)*(p-1) — because at each step it drops to something less than p.

To simplify this equation, we take out the “mod p” because in arithmetic modulus p, “mod p” is free. Also, 0 and p are the same number. (yes, that’s weird.)
We can knock a few steps off this procedure:

`1*2*...*(p-1) + 1 = 0 = p1*2*...*(p-1) = p - 1    1*2*...*(p-2) = (p-1)/(p-1) = 11*2*3*...*(p-2) = 1`

In Haskell, we can write this as: for a list of numbers from 1 to p-2, accumulate a product, modding the product by 17 at each step; see whether the end result is 1.

`let isPrime p = foldl (\acc x -> acc * x `mod` p) 1 [2..(p-2)] == 1`

This says start with 1, accumulate the product-then-mod, and see whether we come out with 1.

Let’s check all the numbers up to 100 and see which ones are prime.

`>filter isPrime [2..100][2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61,67,71,73,79,83,89,97]`

That’s pretty much instantaneous. Finding every prime between 2 and 10000 — there are 1229 — takes about thirty seconds on Swirlybob in GHCi. It was twenty seconds on Carolyn, my work laptop.

A future goal: use monads to time the operation precisely. I wonder whether implementing it as a recursive function and explicitly checking for 0 in the accumulator will be faster. (This happens early in the process for all non-primes except perfect squares.)

# Spring configuration returns home

Remember the early days of XML, when people were all excited about using it to wire up applications? “You can change which implementation is used without changing any code!”

Sure, right, XML isn’t code. It gets packaged in your jar files, with the code. It controls how your application works, like the code. It breaks shit when it is wrong, like the code. Difference is, it’s a lot easier to test the code.

I’m happy to see that Spring is coming full circle and moving configuration back to the code. Not to application code (in-bean annotations), but to separate configuration classes. This keeps the wiring isolated from the application code, while keeping the configuration readable, refactorable, and more testable.

Instead place of XML, we get something like this:

`@Configurationpublic void ZooConfig {   @Bean   public Goat goat() {      return new GoatImpl();   }   @Bean   public PettingZoo pettingZoo() {      PettingZoo p = new PettingZooImpl(goat(), goat());      p.openForBusiness();      return p;         }}`

Yes, this is a trivial made-up example. What is exciting about it?
– The compiler finds the typos in your class names.
– The IDE (without any special plugins) links between the application code and configuration. I can get from here to the implementation class quickly. The configuration will show up in a “Find Usages” search. If I rename GoatImpl to AnatolianBlackGoat, the configuration will change, because the configuration is code!
– Integration tests instantiate a fully populated PettingZoo without loading Spring, and without duplicating the logic of how to create one.

`   @Test   public void animalsLikeItWhenIPetThem() {       PettingZoo p = new ZooConfig().pettingZoo();       String result = p.getAnimal().pet();       assertThat(result, is("Maaaaaaaa."));   }`

(Caveat: this isn’t a perfect test. In real life, Spring will proxy the ZooConfig and wrap the goat() call to return one singleton Goat every time. The Spring-created PettingZoo has the same Goat twice. My test PettingZoo has two different Goats.)

This new method of Spring config is a welcome admission that XML is code. If it lives with the code, integrates with the code, and can break the application, then the configuration is code. Pointy braces or curly braces, it’s all code. It might as well be in Java. Putting all this glue and wiring in one place is good, decoupling our application classes is good, and the beautiful part is we can have all this and compile-time type checking too. Mmm, cake.

The phrase “configuration classes” has a lovely ring to it. I hope to hear more of it in the future.

Credit to: St Louis JUG and Chris Hardin of OCI, presenter.

# Guava: pidgin functional programming in Java

Google’s guava library provides a few constructs that let us use functional style in Java. Personally, I enjoy the slightly more declarative style that results, and have a new game of eliminating loops. Unfortunately, it’s Java. Attempting to do anything functionally is rather ugly. What do you think – worth it? or unreadable?

Before:

`   public List getUsefulThings() {       List usefulThings = newArrayList(); // this is also a use of Guava       for(Thing t : listOfThings) {          if (t.isUseful()) {              usefulThings.add(t);          }       }       return usefulThings;   }`

After:

`   private static final Predicate THING_IS_USEFUL_PREDICATE = new Predicate() {                 public boolean apply(Thing input) {                       return input.isUseful();                 }      };   public Iterable getUsefulThings() {      return filter(listOfThings, THING_IS_USEFUL_PREDICATE);   }`

The goal is to make the code in getUsefulThings() state what the method is doing, rather than how it goes about it. “Look through this list and if something is useful, put it in the output” is a “how.” “Filter this list on this predicate” is a “what.”

I like to put the predicate in a static variable largely because it’s hideous (thanks to Java syntax). The name of the predicate is more readable than the predicate itself.

There is one additional subtle advantage to the Guava method of returning useful things: the filtering doesn’t happen until someone uses the output. The Iterable returned is lazy. If the caller only wants the first usefulThing:

`      {         Thing usefulThing = getUsefulThings().iterator().next();         doSomething(usefulThing);      }`

… then with the Guava solution, the predicate checked against items in listOfThings only until the first useful Thing is found. The rest of listOfThings won’t be processed at all. Lazy evaluation in Java!

Then again, this lazy evaluation can have an even more subtle effect: if the contents of listOfThings are modified after the call to getUsefulThings() but before that Iterable output is used, the Iterable will reflect the updated listOfThings. This goes to prove: mutability is evil.

# A small correction: F# active patterns

In Professional F# 2.0, an example is given for using an active pattern to match against a struct. The example could be improved.

The original:

`[]type Point(x : int, y : int) =    member pt.X = x    member pt.Y = y    override pt.ToString() =        System.String.Format("({0},{1})", x, y)let newPoint = new Point(0,0)let (|Point|) (x : int, y : int) (inPt : Point) =    inPt.X = x && inPt.Y = ylet message =    match newPoint with    | Point(0, 0) true -> "You're at the origin!"    | Point(12, 12) true -> "You're at 12,12!"    | _ -> "Who knows where you are?"System.Console.WriteLine(message) // output: "You're at the origin!"`

Here, the active recognizer Point takes two arguments: the desired x and desired y value. The output is a boolean, which is then matched against the pattern “true” in each case.

This would be slightly more succinct and far more flexible if the active recognizer didn’t try to do comparisons, and simply broke down the Point into its components.

`type Point( x : int, y: int) =    member pt.X = x    member pt.Y = ylet newPoint = Point(0,12)let (|Point|) (inPt : Point) =   (inPt.X, inPt.Y)let message = match newPoint with               | Point(0,0) ->  "You are at the origin"               | Point(0,_) ->  "You are on the y axis"               | Point(_,0) ->  "You are on the x axis"               | Point(x,y) when x = y ->  "You are on the line x = y"               | Point(x,y) ->   "you are at (" + (string x) + "," + (string y) + ")"printfn "%s" message // output: "You are on the y axis"`

Here, the active recognizer has fewer arguments and returns the int*int tuple representation of a Point. This tuple is then matched against (0,0) or whatever location we care about.

This is more succinct because we don’t have to put “true” at the end of each pattern. It is more flexible because we can use a wildcard (underscore) for either the X or Y property of the point. Even better, we can put a variable name in for either or both, and then that variable can be used in the guard clause (“when x = y”) or the result expression.

For the record, I post this here at the suggestion of an author. Why submit an idea privately when people volunteer for public ridicule?

# Why?

Why speak and write about programming? Because I can! Because this is an ambition that I’m suited for, that excites me, and that complements and enhances the rest of my career.

I’m a good developer, but had nowhere further to go with it until now. Project management? No thank you, I’ll stay technical. Architecture? I’ve never met an architect whose job I coveted.

Speaking and writing? Yes!

I grew up with this. My grandparents (James and Marti Hefley) were authors and publishers. Grandmarti was also Drama Director at the local college, so I traveled with the drama troupe and learned to project. I did copyediting for the books they published. I was embarrassed my last day of high school when the Honors English teacher excoriated the class for poor structure on their essays – “except for Jessi. She’s a better writer than 99% of people so she doesn’t have to follow these rules.” I’m well-suited for this.

I chose programming for this. My first internship, halfway through college, started with learning Arc/INFO. That took about two weeks, with no prior training in programming. My strength as a developer is not breadth of experience, but speed of learning new systems. Speaking and writing is all about learning – new paradigms, new topics, whatever people want to hear about, learn it and then write about it. All this learning is exciting.

I work at a good company for this. Daugherty is all about developing our consultants. I get opportunities to practice speaking, they get training for other consultants. I learn new frameworks and languages, our clients get more suggestions on what tools might help them. When I travel to conferences and write articles for technical website, Daugherty gets exposure. My managers at Daugherty provide advice, encouragement, and mentorship. It’s a great consulting company, and I will be a great asset.

All this adds up to a brand-spanking-new motivation to love my profession. I have a lot of catching up to do, a little bit of free time to do it in, and a couple key new friends to help me out. So far, this is a great adventure.

Enough about me. From now on, this technical blog is for technical posts about techinal-ologies. Technologies. For more personal info, look me up on Google Plus.

# Why now?

My friends notice that I’m different lately. Instead of telling drinking stories, I’m reflecting about functional programming concepts. Instead of rushing out of the house as soon as the kids are in bed, I’m holing up at my new desk. I’m showing up to meetings where no beer is available. Crazy stuff!

Way back in 2006, I attended my first No Fluff Just Stuff, the weekend Java conference. The speakers – some local, most not – taught about programming languages, techniques, frameworks. Most of the speakers were informative and inspiring. I thought, “I could do that. It would be fun to speak at a conference like this, to get people excited about their jobs.” Yet, while I love being in front of people, I wasn’t going to chase after a career in it – much easier to stick with back-end Java development. I put the thought out my head and concentrated on having children. No Fluff was one weekend per year until I switched jobs, and then the new jobs didn’t send me.

This spring, that distant forgotten dream jumped into focus. My favorite No Fluff speaker presented at the local Java User Group. I didn’t make it there early enough to learn about Java 7, but there was time to ask him out for a beer afterward. There’s nothing like a life-changing rendezvous at the Hive.

When Ted Neward and Ken Sipe tell me I would be a good speaker, and they can introduce me to people and provide recommendations, then this world is wide open. Somehow that martini is not as tasty, and I’d rather be at my laptop than in a bar. Learning new languages and paradigms is more stimulating than live music, intellectual conversation more refreshing than a cold beer.

Don’t worry guys, I can still cut loose and party – just ask the guys at St. Louis Day of .NET. But watch out – I’ve always had a ton of potential, and for the first time I have some ambition to go with it.