Tuesday, May 17, 2011

Grails - Querying complex associations

Criteria class allows performing complex searches on grails objects. There are number of shortcut methods for performing queries but these methods have limitations in terms of number of conditions used in "where clauses". Traditional sql "joins" are not possible as shown in some of the Grails "Finder" methods shown below.



Sample 1:
def list = AccountTransaction.findAllByCompanyCodeAndVoucherDateBetween(branch, fromDate, toDate, params)

Sample 2:
def list = AccountTransaction.findAllByCompanyCodeAndVoucherDateGreaterThanEquals(branch, fromDate, params)

Sample 3:
def list = AccountTransaction.findAllByCompanyCodeAndTransGroup(branch, group, params)




"params" contains attributes related to sorting, paging etc. It is very easy to use finder methods but when you want to filter objects by more conditions we need to look for alternatives.

For understanding the relationships used in this sample, I listed the grails domain classes.



class TransactionType{
String code
String description

}

class AccountTransactionGroup{
static belongsTo = [transactionType: TransactionType, ..]
Date createDate
..
..
}

class AccountTransaction{
static belongsTo = [transGroup: AccountTransactionGroup, ...]
Date voucherDate
String companyCode
..
..
}




The Criteria closure used in the example below will allow us to query transactions of the given transaction type , say, "Journal" or "Receipt" or "Payment". Note the usage of "createAlias" used to link the objects.
AccountTransaction is linked to AccountTransactionGroup.
AccountTransactionGroup is linked to TransactionType.
TransactionType has the property we are trying use in the query. ("code")

The variable 'varr' contains list of transaction codes, one or more. 'in' is used to specify this list in the closure.




def lst = AccountTransaction.createCriteria().list{
createAlias ('transGroup', 'tg')
createAlias('tg.paymentTransaction', 'tgp')
createAlias('tgp.transactionType', 'tgpt')
if (branch != null) eq('companyCode',branch)
if (toDate == null) eq('voucherDate', fromDate)
else between ('voucherDate', fromDate, toDate)
varr.size>0?'in' ('tgpt.code', varr):''
order (params.sort,params.order)
maxResults (params.max)
firstResult (params.offset)
}

Saturday, May 14, 2011

Implementing advanced sort in Grails

The "list" pages generated by inbuilt scaffolding/template features of grails have pagination and sorting features. However, if the domain object displayed in the list is a nested object having another domain object as a property, you may notice that sort is not enabled for that field. Boiler plate code for the header of the list is shown below.
















As you would have noticed few columns have sortable columns automatically generated by Grails command, generate-all or generate-views.

The properties 'partyAccount' and 'bankAccount' in this sample are domain classes nested in the domain class 'partyTransaction'. We could convert them to sortable columns by using the tag g:sortableColumn.
eg:
<g:sortableColumn property="party" title="${message(code: 'paymentTransaction.party.label', default: 'Party')}"></g:sortableColumn>


The sort behavior is magically introduced after this modification.

The version of the code generated by grails in the corresponding controller class is shown below:



def list = {
params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.sort = params.sort?:'id'
[ paymentTransactionInstanceList: PaymentTransaction.list( params ),paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}


The sort result may not be as anticipated. How do we enforce sort on a custom property?
An enhanced version to get custom sort behavior is given below:



def list = {

params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.offset = params.offset? params.offset.toInteger():0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'
def lst = PaymentTransaction.createCriteria().list{

createAlias ('partyAccount', 'pa')

if (params.sort == 'partyAccount')
order ('pa.accountName', params.order)
else

order (params.sort, params.order)

maxResults (params.max)
firstResult (params.offset)
}

[ paymentTransactionInstanceList: lst, paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}



We can also implement sort on more than one column as below:



def list = {

params.max = Math.min( params.max ? params.max.toInteger() : 10, 100)
params.offset = params.offset? params.offset.toInteger():0
params.sort = params.sort?:'id'
params.order = params.order?:'desc'
def lst = PaymentTransaction.createCriteria().list{

createAlias ('partyAccount', 'pa')
createAlias ('bankAccount', 'ba')

order ('pa.accountName', 'asc')
order ('ba.accountName', 'asc')

order (amount, 'desc')
maxResults (params.max)
firstResult (params.offset)
}

[ paymentTransactionInstanceList: lst, paymentTransactionInstanceTotal: PaymentTransaction.count() ]
}



Sample classes used in the example:

class PaymentTransaction{
static belongsTo =[partyAccount:Account, bankAccount:Account, ...]
double amount
..
..


}

class Account{
String accountName
..
..
}





Note: In case the property 'partyAccount' is nullable you may force LEFT JOIN to get the right results.


createAlias ('partyAccount', 'pa', CriteriaSpecification.LEFT_JOIN)

Monday, April 25, 2011

SVN - Version control system on Amazon EC2



There are number of hosted SVN providers who offer hosting services along with add-on services like project management, bug tracking etc. If your requirement is purely to host the version control system we may end up paying little bit premium for the add-on services. The charges tend to increase when your storage requirements increases. You may compare the prices offered by Unfuddle and Beanstalk. If your storage requirement is about 10 GB and you have would like to install your own SVN server, you can save significantly by going for Amazon EC2 micro instance. I detailed the steps below to get your SVN server up and running.

1) Go for reserved Amazon EC2 instance on a long term plan ( 1 year/3 years). Choose micro instance that costs about $54 annually and a monthly recurring fee about $7.

2) Launch new instance from EC2 tab of Amazon web services customer portal.

3)Choose Basic 32-bit Amazon Linux AMI and Continue to next option.

4)

In the above screen choose Instance type as "micro". If you opted for a "Reserved Instance" make sure you select the "Availability Zone" matching the zone you selected for purchasing "Reserved Instance". Further steps in the above screen will guide you to complete the process.


5) Once your Amazon EC2 instance is up and running you have to create a keypair to allow access from SSH terminal or Putty from a windows PC.



The option in the above screen lets you create a keypair and can be downloaded locally as yourkey.pem file.

If you choose to use Putty you may find this link very useful.



6) Once you launch your terminal, login as "ec2-user"

7) You need SVN server software and Apache webserver to access SVN using http.

Install Apache.

> sudo yum install httpd

> sudo service httpd start

To test the apache from your browser.

- check the public dns name of your ec2 instance. This is typically of the form ec2-xx-xx-xx-xx-xx.compute-1.amazonaws.com

You may type this url to check your running version of apache.
By default it is not accessible due to certain security permissions. You have to enable access using "Security Groups" under Network & Permissions.



Create a new inbound rule as shown in the following picture. Choose HTTP as a New rule using the tab "Inbound".




Access the Apache server with the URL mentioned in the beginning of this step 7.

8) SVN setup

Detailed step given in the link below.