Database « Intelligrape Groovy & Grails Blogs

Archive for the ‘ Database ’ Category

How to Map database table without id with grails domain

Tuesday, September 7th, 2010

Recently I got a query regarding mapping a database table which do not have any id and version. For example the table have two varchar fields username and password nothing more than that.
Although it was something strange for me that table doesn’t have the id field. The good thing is that the username is a primary key in the table and this is not auto incremented user want to create it by his own method.

The good thing about grails is, in most of the cases you get your answer in the docs http://grails.org/doc/latest/ . So in this case we just need to change the id field in grails domain like this

class Test {
    String username
    String password
 
    static mapping = {
        id name: 'username'
        version false
        id generator: 'assigned'
    }
    static constraints = {
        username(nullable: true)
    }
}

and we are done :) .
Hope it helps
## Uday Pratap Singh ##
uday@intelligrape.com

http://www.IntelliGrape.com/
http://in.linkedin.com/in/meudaypratap

  • Share/Bookmark
Posted in Database, Grails

MySql : Publish results of a query to a text file

Monday, August 16th, 2010
Posted by Himanshu Seth

A while back I was trying to debug an error in our application logic. For that I had to analyze the results of a query

This analysis was getting pretty tough to do on the command line. So, I tried to find a way to transfer the results of a query to a text file so that they can be analysed easily.

With help from google, I got my answer at http://www.wellho.net/forum/The-MySQL-Relational-Database/MySQL-query-to-a-text-file.html

echo "<YOUR SELECT QUERY>"| /usr/local/mysql/bin/mysql --user=root --password=password dbName > /path/to/your/textFIle.txt/

Hope this helps

  • Share/Bookmark
Tags:
Posted in Database

Grails Transactions using @Transactional annotations and Propagation.REQUIRES_NEW

Friday, July 30th, 2010
Posted by Abhishek Tejpaul

Hi All,

Here is how you can implement a new transaction in an already executing transaction in Grails which uses nothing but Spring framework’s Transaction mechanism as an underlying implementation. Spring provides @Transactional annotations to provide declarative transactions. We can use the same in our Grails project to achieve the transactional behavior.

Here is the scenario: You have two domain classes named SecuredAccount and AccountCreationAttempt. You try to transactionally save the SecuredAccount object which in turn creates a AccountCreationAttempt object which writes to the database stating: “There is an attempt to create a new SecuredAccount at this time: <current date and time>”. Point to note here is that even if the creation of the new SecuredAccount object fails, the record must still be written to the database so that the Administrator can validate whether the attempt at the specific time was by a legitimate user or an attacker.

Here is the code:

import org.springframework.transaction.annotation.*
Class MyService {
 
static transactional = false
def anotherService
 
@Transactional
def createSecuredAccount() {
def securedAccount = new SecuredAccount(userId:"John")
securedAccount.save(flush:true)
anotherService.createAccountCreationAttempt()
throw new RuntimeException("Error thrown in createSecuredAccount()")
}
}
import org.springframework.transaction.annotation.*
class AnotherService {
 
static transactional = false
 
@Transactional(propagation = Propagation.REQUIRES_NEW)
def createAccountCreationAttempt() {
def accountCreationAttempt = new AccountCreationAttempt(logRemarks: "There is an attempt to create a new SecuredAccount at this time: {new Date()}")
accountCreationAttempt.save(flush:true)
}
}

Now in this scenario, AccountCreationAttempt object always gets persisted whether or not the transaction for creating SecuredAccount object fails.

Here are few gotchas regarding the above transactions:

1.) First of all, for Propagation.REQUIRES_NEW to work as intended, it has to be inside a new object i.e. a new service in our example. If we had put the createAccountCreationAttempt() method in the MyService there would be no new transaction spawned and even no log entry would be made. This is Spring’s proxy object implementation of transactions and you can read more about it here:

http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/transaction.html#transaction-declarative-annotations.

Please pay special attention to the “NOTE” sub-section.This is what it states:

“In proxy mode (which is the default), only external method calls coming in through the proxy are intercepted. This means that self-invocation, in effect, a method within the target object calling another method of the target object, will not lead to an actual transaction at runtime even if the invoked method is marked with @Transactional.”

2.) Secondly, all the @Transactional methods should have a public visibility i.e. createSecuredAccount() and createAccountCreationAttempt() methods should be public methods, and not private or protected. This again is Spring’s @Transactional annotations implementation and you can read about it at the same link as provided above. Note the right side-bar titled “Method visibility and @Transactional“.

Well, once you keep note of these gotchas I guess you are all set to make good use of @Transactional annotations and its full power.

Cheers !!!

- Abhishek Tejpaul
abhishek@intelligrape.com
[IntelliGrape Software Pvt. Ltd.]

  • Share/Bookmark
Posted in Database, Grails, Groovy

Enum datatype in mysql

Friday, July 23rd, 2010

Recently in a Grails project that I am currently working on, the client wanted to have few functionalities of our web application on iPhone as well. We are using Enums heavily in our project and the iPhone development cannot recognize from looking at the tables that few fields are Enums so those need to be one of value of Enum. So they use to store some different value in it as the datatype of Enums in table is varchar the sql doesn’t mind storing the different values.

So after looking at here and there for Enum datatypes for sql we found a very easy solution to do that. We altered few tables like this

alter table documents change document_priority document_priority enum('Critical','Major','Blocker','Backlog') default 'Major';

For adding an Enum column you just need to do something like

alter table documents add column enum('Critical','Major','Blocker','Backlog')

after doing this the user can see that which values the field expect so iPhone development team also get to know what should they do. But it does not restrict user to other values in it though if user tries to add some other value in it the sql will set it to blank(” “). It still doesn’t serves our purpose because in application there is no blank Enum. The solution for this is in the sql_mode. So we set the sql_mode

SET sql_mode = STRICT_TRANS_TABLES

So can see the sql_mode of your database like this

SELECT @@global.sql_mode, @@session.sql_mode;

I hope this helped for some people the only thing I miss in this. I dont get the solution for this in Hibernate. May be there is a solution for this either in Hibernate or in Grails. Looking for the better solution that can be done in the application itself.
Hope it helps
## Uday Pratap Singh ##
uday@intelligrape.com

http://www.IntelliGrape.com/
http://in.linkedin.com/in/meudaypratap

  • Share/Bookmark
Posted in Database

Tomcat 6 Session Persistence through JDBCStore

Wednesday, July 21st, 2010
Posted by Abhishek Tejpaul

In one of our recent projects, we needed to save the HTTP session in the database.
This blog refers the Apache documentation as found here: http://tomcat.apache.org/tomcat-6.0-doc/config/manager.html

These are the following steps that need to be followed:

Step 1: Create a database named tomcat (as shown in our example in Step 3 below) or any other name as specified in the ‘connectionURL’ attribute of the <Store> element.

Step 2: Create the following table in the newly created database:

create table sessions (
session_id     varchar(100) not null primary key,
valid_session  char(1) not null,
max_inactive   int not null,
last_access    bigint not null,
app_name       varchar(255),
session_data   mediumblob,
KEY kapp_name(app_name)
);

Step 3: Copy the context.xml file available at the global level at this location: <TOMCAT_HOME>/conf to your application’s META-INF folder and replace the <Manager> element with the following:

<Manager className='org.apache.catalina.session.PersistentManager'
                saveOnRestart='false'
                minIdelSwap='0'
                maxIdleSwap='0'
                maxIdleBackup='1'>
<Store className="org.apache.catalina.session.JDBCStore"
driverName="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost/tomcat?user=username&amp;password=password"
sessionTable="sessions"
sessionIdCol="session_id"
sessionDataCol="session_data"
sessionValidCol="valid_session"
sessionMaxInactiveCol="max_inactive"
sessionLastAccessedCol="last_access"
sessionAppCol='app_name' />
</Manager>

If these settings are placed in the ‘<TOMCAT_HOME>/conf/context.xml‘, it will have a global effect on all the applications running on the server. For an application specific setting, you can place a newly created “context.xml” file in the <APP_HOME>/META-INF sub-folder inside the webapps folder.
Please note that attributes used in the <Store> element make use of the table columns as created in the database in Step 2.

Step 4: Set the following system properties named – ‘org.apache.catalina.session.StandardSession.ACTIVITY_CHECK‘ to ‘true‘.
For further information on this, read the ‘Persistent Manager Implementation’ section at this link : http://tomcat.apache.org/tomcat-6.0-doc/config/manager.html

Step 5: Make sure you have placed the MySql jar in the <TOMCAT_HOME>/lib folder.

Now you can try to hit the application’s URL and check the database table to see the newly persisted session. Please note that it takes around 60 seconds to see the stored session in the database so you might have to wait a bit. You can have multiple instances of tomcat running your application pointing to the same database and can share this persisted session in case any of the tomcat instance crashes.

NOTE: Please make sure if you make any changes to the “context.xml” file, you have to delete the following xml file located in the <TOMCAT_HOME>/conf/Catalina/localhost/{yourAppName}.xml. If you don’t delete this file, then your changes will be ignored and the settings defined in this file will take effect.

Cheers!!!

Abhishek & Imran

abhishek@intelligrape.com | imran@intelligrape.com

[Intelligrape Software Pvt. Ltd.]

  • Share/Bookmark
Posted in Database, Grails

How to generate DDL script for domain classes in Grails using schema-export target

Thursday, July 15th, 2010
Posted by Deepak Mittal

On most of my grails projects, I am used to let grails handle database schema creation for me. However, there is a project where the database is shared between multiple applications. In that project, we have to check-in our table creation/modification script as a SQL file, which is later executed by a DBA.

I use a not-so-commonly known Grails command/script schema-export to help me deal me with this situation without much fuss of writing the DDL script myself.

To generate DDL script for all the domain classes

> grails schema-export

The DDL script in this case is created as {PROJECT_ROOT}/target/ddl.sql file.

You can also specify the path of the file where you want the schema file to get generated.

> grails schema-export sql/ddl.sql

Hope this helps!

-Deepak

  • Share/Bookmark
Posted in Database, Grails

Drop unique constraint in MySQL

Friday, July 2nd, 2010

Recently in my Grails project I mentioned a domain field as unique in constraints. So the table automatically created also had the same constraint, but as the requirement changed the field was not supposed to be unique. Now we need to remove that constraint from the database table as well. The trick in MySQL is that it stores the unique constraint fields as an index and not like other constraints so we cannot do the drop constraint for a unique constraint. The simple solution for this is to drop the index like this:

alter table person drop index address

It worked.

## Uday Pratap Singh ##
uday@intelligrape.com

http://www.IntelliGrape.com/
http://in.linkedin.com/in/meudaypratap

  • Share/Bookmark
Posted in Database

Clearing Hibernate Query Cache in Grails

Wednesday, June 23rd, 2010
Posted by Vivek Krishna

In one of the projects, we had used Query Caching to improve the performance. However, it also meant that the updates/inserts into a table did not get reflected immediately, i.e. something like:

DomainClass.findByPropertyName("propertyName", [cache: true])

returned the same list as it was, before the insertion/updation took place. We found that this could be resolved by clearing the query cache every time an update took place. This was done in Grails by making a call:

sessionFactory.queryCache.clear()

Make sure that the artefact(controller, service, taglib, job etc.) has sessionFactory injected into it using the line:

def sessionFactory

Hope this helps.

– Vivek

vivek[at]IntelliGrape[dot]com

http://in.linkedin.com/in/svivekkrishna

  • Share/Bookmark
Posted in Database, Grails, Java tools

Grails criteria query example for unidirectional one-to-many relationships

Monday, June 14th, 2010
Posted by Aman Aggarwal

Following is an example of unidirectional one-to-many relationship:

class Employee {
    String name
    static hasMany = [roles: Role]
}
 
class Role {
    String name
}

How can we find all the employees with a particular role – “Grails Developer”?

Role role = Role.findByName("Grails Developer")

One way of doing this can be:

Employee.list().findAll{role in it.roles}

This is an inefficient way since, we are fetching all the records from the database every time. Also, in case we are showing paginated view of results, we will need to manage pagination ourself.

A better way of this will be using createCriteria() query:

List<Employee> employees = Employee.createCriteria().list{
    roles{
        eq('id', role.id)
    }	
    firstResult(20)
    maxResults(20)	
}

I am wondering what will be the query in case Role.groovy is an enum.


~Aman Aggarwal
aman@intelligrape.com

http://www.IntelliGrape.com/

  • Share/Bookmark
Posted in Database, Grails

Changing Location of MySql DataStore

Monday, June 7th, 2010
Posted by Vivek Krishna

On one of our projects, we had to store the MySql Data at a location different from the default location. This was needed because the server was on an instance-store Amazon instance which meant that the data would get lost if the instance had to be rebooted for some reason. After going through the tutorials and articles on AWS, we came across this excellent piece on how to setup MySql Data store on an EBS volume.

Later on, I tried out the steps on the local machine and see if we could move the MySql data store to some other location on our system. As it turned out, we could and here are the steps I followed.

Stop mysql server running on your system with the command

sudo /etc/init.d/mysql stop

Now, create folders named etc, lib and log at any location where you wish to save your mysql data. Let us keep them inside a folder called “/mysqldata”

mkdir etc lib log

Now, back-up your mysql data by copying the contents of /etc/mysql, /var/lib/mysql and /var/log/mysql to another location. Then, move the folders /etc/mysql, /var/lib/mysql and /var/log/mysql to the “/mysqldata”

mv /etc/mysql /mysqldata/etc
mv /var/lib/mysql /mysqldata/lib
mv /var/log/mysql /mysqldata/log

Let us now create new folders /etc/mysql, /var/lib/mysql, /var/log/mysql

mkdir /etc/mysql
mkdir /var/lib/mysql
mkdir /var/log/mysql

Now these new directories need to be mapped with the ones we moved to “/mysqldata”
This is done by setting the mount point for each of these directories to the ones which we created.
To do so, appending the following entries to “/etc/fstab”

mkdir /etc/mysql
/mysqldata/etc/mysql /etc/mysql none bind
/mysqldata/lib/mysql /var/lib/mysql none bind
/mysqldata/log/mysql /var/log/mysql none bind

With this done, let us mount all the entries by issuing the command

mount -a

MySql data store is now at another location.

Hope this helps

  • Share/Bookmark
Tags: , ,
Posted in Database, Linux