Sunday, August 31, 2008

Automate Data Transfer and Web Actions Using cURL

If you have ever had the need to automate some action such as transferring files using a secure protocol such as SFTP, or if you have ever thought that it would be nice to automate some sort of web action, you need to know about cURL. According to their website:
curl is a command line tool for transferring files with URL syntax, supporting FTP, FTPS, HTTP, HTTPS, SCP, SFTP, TFTP, TELNET, DICT, LDAP, LDAPS and FILE. curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, kerberos...), file transfer resume, proxy tunneling and a busload of other useful tricks.
I have actually used cURL in a few projects now and I am very impressed with it's rich set of features and reliability to automate tasks. I won't go over every possible use for curl, but I'll show one example of how I have used cURL in a past project.

I recently setup a text messaging alert using Google's SMS system and Microsoft Outlook. Based on certain types of messages, Outlook will kick off a batch file, that contains one command:

curl -d mobile_user_id="555-555-1234" -d carrier="ATT" -d text="TEXT MESSAGE DATA"
-d gl="US" -d hl="en" -d client="navclient-ffsms" -d from="" -d source="" -d c="1"
http://www.google.com/sendtophone
In the command above, all of the -d parameters are post parameters that will be sent to the Google SMS site and they are fairly self explanatory. After the parameters, we have the URL http://www.google.com/sendtophone which is where the Google SMS form is located.

If you are looking for other ways to use cURL, check out their manual.

Saturday, August 30, 2008

PL/SQL MERGE Query ( UPSERT )

One of my recent findings about PL/SQL is the MERGE query, also known as an UPSERT. This is exactly what it sounds like, an INSERT and UPDATE all in one block of code. This can be really useful and can really reduce the amount of code you have to write to perform certain operations. On top of reducing the amount of code, it can really make your code more readable and easier to maintain.

Basic MERGE Syntax

MERGE INTO destination_table
USING (SELECT key_column, value_column FROM source_table)
ON (destination_table.key_column = source_table.key_column)
WHEN MATCHED THEN
UPDATE SET destination_table.value_column = source_table.value_column
WHEN NOT MATCHED THEN
INSERT (destination_table.key_column, destination_table.value_column)
VALUES (source_table.key_column, destination_table.value_column);
Looking at the basic syntax above, you get an idea of how the MERGE query works. You can make this as basic or as complicated as you would like. Of course, you can use JOINS, VIEWS, etc. in either the source or destination queries. You can leave off either of the WHEN MATCHED THEN or WHEN NOT MATCHED THEN blocks if you don't want to perform operations in those certain situations.

Let's take a look at an example scenario where the MERGE query could be used. In our example, we have two tables. In one table, we maintain a list of widgets and in another table we keep the properties for those widgets. The tables with some sample data is listed below.


IDWIDGET
1Super Widget
2Awesome Widget
WIDGET_IDPROP_NAMEPROP_VALUE
1COLORGREEN
1SIZELARGE
2SIZEMEDIUM
In our WIDGET table, we have two widgets. The Awesome Widget and the Super Widget. In our WIDGET_PROPERTY table, we have two properties for the Awesome Widget, size and color. We only have one property for the Super Widget, which is size. Management has decided that all widgets will now come in the color blue. So we need to have a color property for all widgets and the value must be blue.

This could get pretty complicated since some of the widgets already have a color property and we can't add another color property to a given widget. So a simple INSERT won't work unless we delete all color properties first, which isn't very efficient, especially if there is a lot of data.

We could use an UPDATE query for the ones that already exist, but not all widgets have a color property, so this will still not solve that problem. With a MERGE query, we can solve this problem with a single statment in PL/SQL. Here is the PL/SQL code we would use to handle this situation.

MERGE INTO WIDGET_PROPERTY DESTINATION
USING ( SELECT WIDGET.ID, 'COLOR' AS PROP_NAME FROM WIDGET ) SOURCE
ON ( DESTINATION.WIDGET_ID = SOURCE.ID AND
DESTINATION.PROP_NAME = SOURCE.PROP_NAME )
WHEN MATCHED THEN
UPDATE SET DESTINATION.PROP_VALUE = 'BLUE'
WHEN NOT MATCHED THEN
INSERT ( DESTINATION.WIDGET_ID, DESTINATION.PROP_NAME, DESTINATION.PROP_VALUE )
VALUES( SOURCE.ID, SOURCE.PROP_NAME, 'BLUE' );

Friday, August 29, 2008

Associative Arrays: Hashtables for PL/SQL

I have been developing software in Java most of my career, but recently my job has required me to take on more projects using Oracle's PL/SQL. Being a Java programmer, I am very well versed in the various collections that are available from the Java API. One collection I find myself using a lot is the Hashtable and recently during a PL/SQL project, I had an algorithm that would be served nicely by such a data structure.

That's when I found out about Associative Arrays in PL/SQL. An associative array is a key-value pair that we can use in very much the same fashion we would use the Hashtable type in Java. In this post I'll show you how to declare a PL/SQL associative array, assign values to the array, then how to access those values. Then I'll go over how to iterate over the PL/SQL associative array.

Declaring, Assigning, and Accessing Associative Arrays

DECLARE

-- Declare our array type to be keyed on 5 byte varchar and hold values of 256 byte varchar.
TYPE zip_city_type IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(5);
-- Declare our actual associative array using our new data type.
zip_city_array zip_city_type;

BEGIN

-- Let's add some values to our array.
zip_city_array('80201') := 'DENVER, CO'; -- Adds entry for zip code 80201
zip_city_array('48201') := 'DETROIT, MI';

-- Print out the values from our associative array.
dbms_output.put_line( 'Zip code 80201 is in ' || zip_city_array('80201') );
dbms_output.put_line( 'Zip code 48201 is in ' || zip_city_array('48201') );

END;
Iterating Through an Associative Array

You will come across situations where you need to iterate through all of the elements in your associative array and you want to do so efficiently. Let's take a look at how we would accomplish iterating through the array we declared above.

DECLARE

-- Declare our array type to be keyed on 5 byte varchar and hold values of 256 byte varchar.
TYPE zip_city_type IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(5);
-- Declare our actual associative array using our new data type.
zip_city_array zip_city_type;
zip VARCHAR2(5);

BEGIN

-- Let's add some values to our array.
zip_city_array('80201') := 'DENVER, CO'; -- Adds entry for zip code 80201
zip_city_array('48201') := 'DETROIT, MI';

-- Assign the first key in our array to zip.
zip := zip_city_array.FIRST;

-- Loop through the array while our key is null.
WHILE zip IS NOT NULL LOOP
dbms_output.put_line( zip || ' = ' || zip_city_array(zip) );
zip := zip_city_array.NEXT( zip );
END LOOP;

END;

Monday, August 25, 2008

Java Source Reflection with JaxMe JavaSource

Alright, it may not happen very often that you need to be able to use reflection on your Java source code, but its nice to know there is a framework that can help you out. Apache's JaxMe JavaSource framework is a framework that can be used for generating Java source files.

I personally found myself working on a project that had to be capable of integrating our source code with another vendor's source code, both of which changed frequently. I was able to use the JaxMe JavaSource framework to verify that all necessary methods had been implemented on our subclasses, and if they hadn't, I was able to stub them out quickly and easily so that it didn't break the build everytime a base class was modified.

For more information on how to use the JaxMe JavaSource framework and in particular the source code reflection portion of the framework, go here.

Sunday, August 24, 2008

Introducing BeanShell: A Java Source Interpreter

If you haven't ever checked out BeanShell before, I would highly suggest you mosey on over to www.beanshell.org and take a look. They describe BeanShell as a small, free, embeddable Java source interpreter with object scripting language features, written in Java.


Essentially, it is a way to run Java source code as an interpreted script. Along with supporting the full Java syntax, it adds a few other nice scripting features such as loosely typed variables. Some of the benefits of such an application include rapid prototyping, rules engines, configuration, testing, embedded systems, and even Java education.


Also, if your application is using Spring, you can even inject BeanShell scripts into your Spring beans. They provide a quick and easy tutorial to get you up and running with BeanShell here.

Saturday, August 23, 2008

Injecting BeanShell Scripts into a Spring Bean

One really neat feature about Spring that you don't hear very much about is the ability to inject scripting into a Spring bean. This is very powerful functionality that allows you to add dynamic logic into your application and even has the ability to reload the script at a set interval enabling you to make code changes on the fly without rebuilding. There are currently three scripting languages that you can use with this feature and they are Groove, Ruby, and BeanShell. This post will focus on injecting a BeanShell script into a Spring bean.


We are going to take a look at a situation in which we are going to implement various rules for calculating invoice amounts for ACME Consulting Company's clients. Some of ACME's clients pay a set monthly fee that entitles them to a given number of hours of service, then they pay an hourly rate for any hours over the set amount. They also have some clients that only pay an hourly fee for service and don't have a monthly contract setup.


For this situation, we are going to setup two invoice calculation rules, one for calculating invoice amounts for our monthly contracts, and one that only calculates invoices for our hourly contracts.


Before we take a look at the code, you'll need to add the BeanShell library to your class path which can be downloaded from www.beanshell.org.


First, we'll create a Java class called InvoiceData that will hold all of the information our rules will need to calculate the invoice amounts:



package com.billing.invoice;

import java.math.BigDecimal;

public class InvoiceData {

private int hoursWorked;
private int hoursEntitled;
private BigDecimal monthlyRate;
private BigDecimal hourlyRate;

public void setHoursWorked( int hours ) {
hoursWorked = hours;
}

public void setHoursEntitled( int hours ) {
hoursEntitled = hours;
}

public void setMonthlyRate( BigDecimal rate ) {
monthlyRate = rate;
}

public void setHourlyRate( BigDecimal rate ) {
hourlyRate = rate;
}

public int getHoursWorked() {
return hoursWorked;
}

public int getHoursEntitled() {
return hoursEntitled;
}

public BigDecimal getMonthlyRate() {
return monthlyRate;
}

public BigDecimal getHourlyRate() {
return hourlyRate;
}

}


This is your basic plain old java object (POJO) that will hold the properties we will need to calculate the total invoice amount.


Next, we'll create a Java interface called InvoiceCalculationRule:



package com.billing.invoice.rules;

import java.math.BigDecimal;
import com.billing.invoice.InvoiceData;

public interface InvoiceCalculationRule {

public BigDecimal calculate( InvoiceData invoiceData );

}


This interface contains only one method named calculate, that accepts an invoiceData object. This interface will be used to perform our invoice calculations. The actual implementations are coming up next and they are our BeanShell scripts.

Now we are ready to build our rule classes. These will be BeanShell scripts, but the syntax is exactly the same as Java which is another reason BeanShell is a great option; no need to learn a different language to implement scripting.

Our first rule, MonthlyContractsInvoiceCalculationRule will calculate invoices for our monthly contracts:



import java.math.BigDecimal;
import com.billing.invoice.InvoiceData;

public BigDecimal calculate( InvoiceData invoiceData ) {

int chargeableHours = invoiceData.getHoursWorked() - invoiceData.getHoursEntitled();
BigDecimal chargeableHoursAmount = new BigDecimal( 0 );
BigDecimal invoiceAmount = new BigDecimal( 0 );

if ( chargeableHours > 0 ) {
chargeableHoursAmount = invoiceData.getHourlyRate().multiply( new BigDecimal( chargeableHours ) );
}

invoiceAmount = chargeableHoursAmount.add( invoiceData.getMonthlyRate() );

return invoiceAmount;

}


Our second rule, HourlyContractsInvoiceCalculationRule will calculate invoices for our hourly contracts:



import java.math.BigDecimal;
import com.billing.invoice.InvoiceData;

public BigDecimal calculate( InvoiceData invoiceData ) {

BigDecimal invoiceAmount = invoiceData.getHourlyRate().multiply(
new BigDecimal( invoiceData.getHoursWorked() ) );

return invoiceAmount;

}


Now that we have all of our classes setup, we need to work on our Spring configuration file so that we can actually retrieve our rules when we need them. Here's an example of what our configuration file would look like:



<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:lang="http://www.springframework.org/schema/lang"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/lang
http://www.springframework.org/schema/lang/spring-lang-2.0.xsd">

<lang:bsh id="hourlyContractsInvoiceCalculationRule"
script-source="classpath:com/billing/invoice/rules/HourlyContractsInvoiceCalculationRule.bsh"
script-interfaces="com.billing.invoice.rules.InvoiceCalculationRule"
refresh-check-delay="60000" />

<lang:bsh id="monthlyContractsInvoiceCalculationRule"
script-source="classpath:com/billing/invoice/rules/MonthlyContractsInvoiceCalculationRule.bsh"
script-interfaces="com.billing.invoice.rules.InvoiceCalculationRule"
refresh-check-delay="60000" />

</beans>


We have everything in place and we are ready to tie it all together. For this example, I am just writing up a quick test, so it isn't a realistic class, but it will give you an idea of how to use our new rules.



package com.billing.invoice;

import com.billing.invoice.rules.InvoiceCalculationRule;

import java.math.BigDecimal;
import java.math.RoundingMode;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class RuleTestMain {

public static void main( String [] args ) {

ApplicationContext context = new ClassPathXmlApplicationContext( "spring.xml" );
InvoiceData invoiceOne = new InvoiceData();
InvoiceData invoiceTwo = new InvoiceData();

invoiceOne.setHoursWorked( 40 );
invoiceOne.setHoursEntitled( 30 );
invoiceOne.setMonthlyRate( new BigDecimal( 1500 ) );
invoiceOne.setHourlyRate( new BigDecimal( 44.25 ) );

InvoiceCalculationRule monthlyRule =
( InvoiceCalculationRule ) context.getBean( "monthlyContractsInvoiceCalculationRule" );

System.out.println( "Monthly Invoice Amount: $" + monthlyRule.calculate( invoiceOne ).setScale( 2, RoundingMode.HALF_UP ) );

invoiceTwo.setHoursWorked( 40 );
invoiceTwo.setHourlyRate( new BigDecimal( 52.25 ) );

InvoiceCalculationRule hourlyRule =
( InvoiceCalculationRule ) context.getBean( "hourlyContractsInvoiceCalculationRule" );

System.out.println( "Hourly Invoice Amount: $" + hourlyRule.calculate( invoiceTwo ).setScale( 2, RoundingMode.HALF_UP ) );

}

}


When we run this code, we will be calculating the first invoice using the monthly rule, then the second invoice will be calculated using the hourly rule. The great thing is, that if we decide we need to change one of these rules, we can actually update the scripts and it will be automatically reloaded every minute and will become active without having to rebuild the application.

The often unappreciated MINUS SQL query

There are times when you need to verify that a given record is associated with all of a given type of records. In this post, I'll be going over the MINUS statement and show you a practical use for this often under used functionality of SQL.

For this example we'll be looking at customer data for an online application. ACME paper in has unleashed a new paper sales website. They allow the employees of the company to which they sale paper to have access to various sections of their website. In order to have more granular control, they control access the sections at both the user level and the company level. In order for a user to have access to a section, they must both have access at the company level and at the user level. Let's take a look at our tables to get a better idea of what we are dealing with.

IDCOMPANY_NAME
1Awesome Corp.
IDUSERNAME
1jdoe
2bsmith
IDSECTION_NAME
1Main
2Special
COMP_IDUSER_ID
11
12
COMP_IDSECT_ID
11
12
USER_IDSECT_ID
11
12
21

The first table (COMPANY) shows the companies that ACME does business with. The second table (USER) shows the users that are using the new online sales site. The third table (SECTION) shows the various sections that are available on the online sales site. The fourth table (COMPANYUSERJOIN) links the users to the various companies. The fifth table (COMPANYSECTIONJOIN) gives company access to the various sections. The sixth table (USERSECTIONJOIN) gives user access to the various sections.

Management has requested a report of all of the users that don't have access to all of the sections that their company has access to. In comes MINUS to save the day. Minus will return only the rows that are not in both queries. Just like UNION, the data types in both SQL queries must be the same. Let's take a look at the query that will return the data we are looking for.

SELECT SECTION.ID, USER.USERNAME FROM USER
JOIN COMPANYUSERJOIN ON COMPANYUSERJOIN.USER_ID = USER.ID
JOIN COMPANYSECTIONJOIN ON COMPANYSECTIONJOIN.COMP_ID = COMPANYUSERJOIN.COMP_ID
JOIN SECTION ON SECTION.ID = COMPANYSECTIONJOIN.SECT_ID
MINUS
SELECT SECTION.ID, USER.USERNAME FROM USER
JOIN USERSECTIONJOIN ON USERSECTIONJOIN.USER_ID = USER.ID

Looking at the SQL query above, we can see in the first section that we are selecting the section id and the username by joining the section table through what the corporation will have access to. This will return all of the usernames for the corporations, along with all of the section ids the corporation has access to. Then we have minus before the second part of the query. In the second part of the SQL query, we are returning the section ids and usernames based on what section each user actually has access to.

The minus will compare these two SQL queries and remove duplicate records. This will return two types of records. The first type of record returned will be the sections that a company has access to, but that a given user has not been given access to. The second type of record that will be returned will be sections that a user has access to, but that a given corporation does not.  For our exercise, we will assume that a given user can't be given access to a section without the company also having access.

Using the example data above, this query will return a section id of 2 and username of bsmith. This means that bsmith is part of a company that has access to section 2, but bsmith does not have access to this section. We then write up our report for management, cut out of work early, and go grab a cold beer or two with some friends. Thanks to the SQL MINUS query.

Delete multiple lines in a text file using regular expressions and sed

I recently found myself in quite a predicament. I had a very large flat file in a specific format and I needed to delete certain record types. Now, obviously this is very easy with regular expressions if the records are only on one line. However, that was not the case.  The record types I needed to remove took up multiple lines.  Below is an example of the type of record I needed to delete (however since the file spec is proprietary I have changed the actual values):
START PAYMENT_RECORD
123456789 80000 JOHN DOE BANK OF ANYWHERE USA 123 ANY STREET ANYTOWN USA
END PAYMENT_RECORD

However with sed, you can still do a multiple line search and delete from a file with the following command:  
sed '/pattern/{N;N;N;d}' filename  

So using the sed command in my particular record example, I accomplished this task with

sed '/^START PAYMENT_RECORD/{N;N;N;d}' payment_data_file.txt

This command tells sed to find the text START PAYMENT_RECORD at the beginning of the line, then delete that line and the next two lines.  The N portion is the number of lines to be removed, and the d portion tells sed to delete the data matching the pattern.

Thursday, August 21, 2008

Check if a value is in a PL/SQL collection using MEMBER OF

You may find yourself working with a collection in PL/SQL and think to yourself, "Wow, it sure would be great if I could do a quick search of this collection to see if it contains a specific value." Well, never fear, cause I'm going to show you how to do just that using MEMBER OF.

IDNAME
1Black
2Brown
3Blue
4Green
5Orange
6Red
7Purple
8Yellow

Let's say that you have the names of the eight basic colors (according to Crayola) in a lookup table called LKBASICCOLORS that has only two columns, ID and NAME.

In our situation, we want to verify that a given color name is one of our eight basic colors. There are numerous ways to do this in PL/SQL, including hard coding these color names into our code. While in this situation that wouldn't be such a bad idea, but in the real world, values tend to change often and nobody wants to be the poor schmuck that gets to go back through all of the PL/SQL looking for hard coded values. By using member of, you can actually look up these values on the fly, load them into a collection, and verify that our given color is a basic color.


DECLARE

v_color_white varchar2(10) := 'White;
v_color_purple varchar2(10) := 'Purple';

TYPE lkbasiccolor_names IS TABLE OF VARCHAR2(20);
v_color_names lkbasiccolor_names;

BEGIN

SELECT NAME BULK COLLECT INTO v_color_names FROM LKBASICCOLORS;

IF v_color_white MEMBER OF v_color_names THEN
dbms_output.put_line('White is a basic color');
ELSE
dbms_output.put_line('White is not a basic color');
END;

IF v_color_purple MEMBER OF v_color_names THEN
dbms_output.put_line('Purple is a basic color');
ELSE
dbms_output.put_line('Purple is not a basic color');
END;

END;

Now let's take a look at what's going on in the sample above. In the DECLARE, we declare two color name variables. Then we define our collection type as a table of varchar2 values. We move on and declare our actual collection as v_color_names as the collection type of lkbasiccolor_names.

In the first line of the BEGIN section, we load all of our color names into our v_color_names collection. The first test we try is to see if the color white is a member of our v_color_names collection. Since it isn't, it will fall into the else and print out that White is not a basic color. In the next block, since Purple is in our basic color list, it should print Purple is a basic color.

© 2010 Confessions of a Java Programmer, All Rights Reserved