Database « Intelligrape Groovy & Grails Blogs

Archive for the ‘ Database ’ Category

A use case of Bitwise AND

Posted by Mohd Farid on April 15th, 2011

Recently, I used bitwise Anding in my grails project. I am sharing the approach that we followed by means of an example.


Suppose we have a domain class Person which can have multiple attributes like Smart, Intelligent, Talkative etc.

What sort of relationship comes to our mind when we see this?
I believe the obvious answer would be Person hasMany Attributes

Next, what if we want to optimize our design so that the searches on the Person becomes better.

Well, this was the question we were facing in our recent project where we finally used the bitwise AND.
We decided to have one field of type Long in our Person class. We named this as attributes . Soon, I ll be explaining why a long for Attribute…

We ensured that the Attributes are never going to be more than 20-30 . So, it became a nice candidate which could utilize the benefit of Bitwise ANDing.

We associated a binary weight to every instance of Attribute domain class.

The rows in the attribute table of our database looked somewhat like this

id | attribute      | weight
1 | Smart          | 1
2 | Talkative      | 2
3 | Intelligent     | 4
4 | Cooperative  | 8
5 | Ignorant       | 16

So, in order to save a Person who is Smart and Cooperative , we would set his attributes field.
weight of Smart + weight of Cooperative.
ie 1+8 = 9.

Suppose our Person table has some entries as follows:

id  | name  | attributes
1   | Tom   |  9
2   | Fred   |  12
3   | John   | 18

If we want to search a person who is Intelligent we need to have a final query like this

Select * from person where attributes&4 = 4
(4 is the weight of Attribute - Intelligent)

Search a person who is Cooperative and Talkative

Select * from person where attributes&2=2 and attributes&8=8
or more optimally,
Select * from person where attributes&10=10
(2 is the weight of Talkative and 8 is the weight of Cooperative)


Some points to be kept in mind before taking this approach:
There is a maximum limit of 64 values for the field on which we plan to do BitwiseANDing.
Bitwise AND is not directly supported by Hibernate, but we can add a custom function and dialect.



Hope this helped.

Thanks & Regards
Mohd Farid

  • Share/Bookmark
Tags: , , , ,
Posted in Database, Grails
5 Comments »

Groovy: Sort list of objects on the basis of more than one field

Posted by Salil on January 25th, 2011

Usually, when we deal with databases, we don’t face such kind of situation because we query database to get result-set in required order.

But let’s say you have a List of objects that you want to sort on the basis of two fields. Let’s discuss it with an example.


I have a list of Tasks (with date and priority fields). I want to sort these tasks ordered by Date and priority-wise (as second level sorting).

Here, I gonna use Expando class, so I can directly run this in my groovyConsole. But definitely you can use some ‘Task’ class.

Expando a = new Expando(date: Date.parse('yyyy-MM-dd','2011-01-01'), priority:1)
Expando b = new Expando(date: Date.parse('yyyy-MM-dd','2011-01-01'), priority:2)
Expando c = new Expando(date: Date.parse('yyyy-MM-dd','2011-01-02'), priority:1)
Expando d = new Expando(date: Date.parse('yyyy-MM-dd','2011-01-01'), priority:3)

def list = [a,d,c,b]

If sorting was required on the basis of date only, it was very simple.

list.sort(it.date)

But as per our requirements – order by date (first level sorting) and priority (second level sorting). We can do something like following.

list.sort{x,y->
  if(x.date == y.date){
    x.priority <=> y.priority
  }else{
    x.date <=> y.date
  }
}

Well, there could be some better way. If you know please put your comments. But it worked well in my case.



Cheers!
Salil Kalia
Salil [at] IntelliGrape [dot] com
Twitter LinkedIn

  • Share/Bookmark
Tags: , , , ,
Posted in Database, Grails, Groovy, Java tools
8 Comments »

Getting up and Running With Cassandra

Posted by Sachin on January 17th, 2011

Luckily, I got some time outside my usual obligations in the project, to learn something new and I devoted the time to getting up and running with cassandra . Getting starting up with it was a bit bumpy, as the case almost always is when you start with something entirely new.

Lets get Started..

1) First step of course is to get the binary files for the cassandra from here

2) It is assumed that you have got jdk>1.5 installed on your machine and JAVA_HOME variable is set. Now extract the tar file to the location you want (I extracted in the /opt folder and created a soft link to it with name cassandra).

3) Create the necessary directories for cassandra in /var/lib and /var/log folders and change their ownership


sudo mkdir -p /var/lib/cassandra

sudo chown -R "user" /var/lib/cassandra

sudo mkdir -p /var/log/cassandra

sudo chown -R "user" /var/log/cassandra

Now move the extracted cassandra project move to the bin folder inside it (in my case /opt/cassandra/bin) and type the command


./cassandra  -f

The -f switch will ensure that cassandra runs in the foreground and its logs will print to standard output.

Now a few lines similar to a java stack trace will appear. I expected to see some kind of “successful or server running message” but there was none. So if you are not seeing any FATAL or ERROR messages it means you have succeeded in running cassandra. :)

Now notice in that stack trace that your cassandra runs on port 9160 by default.

So to connect to cassandra open a new terminal and start the cassandra CLI available in the same bin folder.


./cassandra-cli

You will see a message like

Welcome to cassandra CLI.

Type ‘help;’ or ‘?’ for help. Type ‘quit;’ or ‘exit;’ to quit.
[default@unknown]

typing help here will show you a list of commands available.

Now, we will connect to the cassandra server instance we started earlier.


connect localhost/9160;

Don’t miss that semicolon ;) . All statements here must end with semicolon.

You will now be connected to a “Test Cluster”. It is the default cluster which comes with cassandra. Cluster is a container which encapsulates many ‘keyspaces’ and keyspaces are things similar to a database in relational DBMS. I better leave the data model topic here and concentrate on task at hand. I intend to take up data model topic in a separate blog.

So, Now we have a cluster to work in.

We will create a keyspace (database in relational DBMS world) and then enter and retrieve some data from it.

First of all lets see what all keyspaces are already available. Invoke


show keyspaces;

The  keyspaces you see are used by cassandra and are not to be intended to be used by the us. So lets make our own keyspace.


create keyspace CustomKeySpace with replication_factor=1;

use CustomKeySpace;

What we have just done is created a new keyspace and started using in. (Quite similar to create database <database-name>; and use <database-name>;) Lets leave replication_factor for now.

[default@unknown]
[default@unknown] use CustomKeySpace;
Authenticated to keyspace: CustomKeySpace
[default@CustomKeySpace]

notice the change from [default@unknown] to [default@CustomKeySpace]. It shows you are a “default” user which earlier was not using a keyspace and is now using CustomKeySpace. Another way to look at it is default user is “logged” into CustomKeySpace.

Next step is to create a table, just that it is called a column-family here.


create column_family user;

lets enter some data to this column family now.


[default@CustomKeySpace] set user ['sachin'] ['lname']= 'Anand' ;
Value inserted.
[default@CustomKeySpace] set user ['sachin'] ['email']= 'sachin[at]intelligrape[dot]com' ;

Value inserted.

Now we created two columns for user ’sachin’ one is called ['lname'] and contains value ‘Anand’, other is called ‘email’ and contains value sachin[at]intelligrape[dot]com.

To count the number of columns for a record


count user ['sachin'];

To retrieve the values from the database — you guessed it we will use get.


[default@CustomKeySpace] get user ['sachin'];
=> (column=656d61696c, value=73616368696e40696e74656c6c6967726170652e636f6d, timestamp=1295199962515000)
=> (column=[java]666e616d65, value=4562656e, timestamp=1295199873677000)
Returned 2 results.

problem here column names and values are coming in hex code here. So, we need to add some metadata to tell what kind of values we are expecting. here we go..


[default@CustomKeySpace] update column family user with column_metadata=[{column_name:lname, validation_class:UTF8Type},{column_name:email, validation_class:UTF8Type}];

Again writing get user command


[default@MyKeySpace] get user ['sachin'];
=> (column=656d61696c, value=sachin@intelligrape.com, timestamp=1295199962515000)
=> (column=666e616d65, value=Anand, timestamp=1295199873677000)
Returned 2 results.

So we have got the results we wanted. So in this small article we learnt how to start cassandra connect to it. create a new keyspace add a column_family and set and get data from it. I will be back with more on this for sure.

Thanks & Regards.

Sachin Anand

Email  : sachin[at]intelligrape[dot]com

  • Share/Bookmark
Tags: ,
Posted in Database
No Comments »

Number Formating using regular expression and format method of String class

Posted by Salil on December 14th, 2010

Hey guys, I found something very useful, so sharing it here.
In one of my project there was a requirement to create a fixed length user-id. Let’s say it was 5 characters ID.


So in other words we required to convert -
‘123′ to ‘00123′ and ‘1000′ to ‘01000′ and ‘12345′ will remain ‘12345′.
It appears very simple, but the thing is how quick we do this.


Earlier I thought to do this by calculating the length of id and prefixing required number of zeros.


Alternate single-line and faster solution is -

      // following will return a String with size of 5 characters and prefixes zeros (if id has digits lesser than 5).
         String.format('%05d', id)
      // Note down, id is a digit here.

That’s all. . isn’t it nice?. We can utilize it at many places like – converting date from ‘1-1-2010′ to ‘01-01-2010′.
Idea is to introduce such kind of utility. If you already knew this, please ignore :-)
please open the ideas – if you know something better than this.


cheers!
salil at IntelliGrape dot com

  • Share/Bookmark
Posted in Database, Grails, Groovy
No Comments »

Database Backup Script for windows

Posted by Amit Jain on December 14th, 2010

In one of the project I am working on, the application needs to be deployed on windows server. To take the database backup, I wrote the script which does the following :

  1. Takes database dump and copy it to the backup folder.
  2. Zip that backup file and rename it to the format “YYYYMMDD_HHMMSS”
  3. Removes  all the backups older than 30 days.

I used mysqldump command to take database backup. To zip the backup file using the command line, I found 7-zip (download) which is freely available.  We need to have forfiles.exe (download) which enables us to remove old backup files (say older than 30 days).

@echo off
CLS
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%
set year=%date:~-4%
set month=%date:~3,2%
if "%month:~0,1%" == " " set month=0%month:~1,1%
set day=%date:~0,2%
if "%day:~0,1%" == " " set day=0%day:~1,1%
set datetimef=%year%%month%%day%_%hour%%min%%secs%
"MYSQL_BIN_PATH\mysqldump" --user=root --password=MY_PASSWORD MY_DATABASE_NAME > "BACKUP_FOLDER_PATH\backup.sql"
"7ZIP_EXE_PATH\7z" a -tzip "BACKUP_FOLDER_PATH\"%datetimef%".zip" "BACKUP_FOLDER_PATH\backup.sql"
del "BACKUP_FOLDER_PATH\backup.sql"
"FORFILES_EXE_PATH\forfiles.exe" /p BACKUP_FOLDER_PATH /s /m *.* /d -30 /c "cmd /c del /q @path

Prerequisites for the above script to work would be :-

  1. Mysql and 7zip installed.
  2. Forfiles.exe is downloaded
  3. All the Path’s in script are replaced correctly.

I also used a background job to run this script automatically every hour on the working days. Though same can be achieved with windows scheduler too.

Hope this helped.

Cheers!
~~Amit Jain~~
amit@intelligrape.com

http://intelligrape.com/

  • Share/Bookmark
Tags: , , , ,
Posted in Database
1 Comment »

GORM feature: Bypass Grails Domain Constraints

Posted by Salil on October 15th, 2010

I didn’t know this earlier, but found a very good feature of GORM – ‘bypass’ constraints or ‘customize’ validation while saving a domain-object. I am not sure if there’s any name for this feature. But I am naming it as ‘bypass constraints’.

if(domainObject.validate(['field1', 'field2'])){
  domainObject.save(validate:false, flush:true)
}

Let’s discuss it with a use case. We create a user table with fields – id, name, email, password.
And email/password are mandatory fields. 

Class User{
    Long id
    String name
    String email
    String password

    static constraints = {
        name(nullable: true)
    }
}

Now there’s a requirement in the application – An existing user can invite a friend.

With the help of ‘bypass constraints’ feature, it’s easier to handle this use-case without actually creating any separate domain to store invited users information.

Following code will create User entry with null password.

User user = new User(name:userName, email:userEmail)
if(user.validate(['email']) && user.save(validate:false, flush:true)){
return true
}


NOTE: To achieve this, you just need to set ‘password’ column as nullable true in your ‘user’ table.

Above example/use-case is just to explain ‘bypass constraints’ feature, nothing more than this. :-)

I hope it might help you somewhere.

Salil Kalia
salil [at] intelligrape.com

  • Share/Bookmark
Posted in Database, Grails, Groovy
2 Comments »

Grails Liquibase plugin: dbDiff tool workaround

Posted by Amit Jain on October 12th, 2010

The current project I am working on, is going through QA. At the same time development of the new features and bug fixing is on and we couldn’t afford to loose the test data. So synchronizing the state of the QA database with the development was becoming a pain. So we decided to use grails liquibase plugin.

I found one great article by Jackob Kulzer. As explained by Jackob Kulzer, dbDiff tool in the plugin is hard coded to compare development and test environment databases. The other option available was to write every changeset manually. But I was afraid, what if I forget to update even a single changeset.

I decided to write the script, which would overwrite my test database with the schema of the QA database. QA database was on another server. So I had to write multiple scripts to make it work. But once done it made my job really easy.

Following are the scripts to be created:

1. qaSchemaDump.sh (To be created on the development machine):


#SSH to the server and execute generateSchema shell script.
#Note: I had created password less login to the server.
ssh amit@qa.myserver.net /home/amit/Scripts/generateSchema.sh

#Copy QA Database schema to the development machine
scp apg@qa.myserver.net:/home/amit/Scripts/qaDBSchema.sql /home/amit/Scripts/liquibase/qaDBSchema.sql

#Import QA Database schema to test database using createTestDBFromQASchema.sql
mysql -u root --password=myPassword < /home/amit/Scripts/liquibase/createTestDBFromQASchema.sql
exit

2. generateSchema.sh (To be created on the Server):


/* Creates QA database schema */
mysqldump --no-data --tables -u root --password=myPassword myQADatabase > /home/amit/Scripts/qaDBSchema.sql

3. createTestDBFromQASchema.sql (To be created on the development machine):

/* drop, create and then use the test database.*/
drop database myTestDB; create database myTestDB;
use myTestDB;

/*Import the QA database schema to the newly created test database*/
source /home/amit/Scripts/liquibase/qaDBSchema.sql
exit

Once all the above mentioned scripts are created, we just need to run ‘qaSchemaDump.sh’ and followed by ‘grails dbDiff’ and the changeset would be available to us on the console. Then copy this changeset, append it to changeLog.xml and commit it to the repository.

With all this done, login to your QA/production server, take database backup and now we can run grails migrate-sql to confirm the sql that would be generated and then finally run ‘grails migrate’ to update QA/Production database.

Thanks to Jackob Kulzer and Nathan Voxland (author of the plugin)

Hope this helped!

~~Amit Jain~~
amit@intelligrape.com

http://www.IntelliGrape.com/

  • Share/Bookmark
Tags: , , ,
Posted in Database, Grails
1 Comment »

GORM Batch Deletes Made Easy with Load Method

Posted by Vivek Krishna on September 29th, 2010

We are using the grails asynchronous mail plugin in our project and noticed that the sent mails never gets deleted from the database. This was an issue because we were sending out a lot of mails and all of them had PDF attachments of about 500 KB to 1 MB each. A sure recipe for disaster because our DB was growing exponentially. To take care of this, we decided to do a DB cleanup and delete the messages that had the status SENT.

Going through the documents for Deleting Objects, we noticed that grails doesn’t really support batch deletes. The executeUpdate() method, which was provided there as a workaround, was also not a suitable solution because we would’ve had to delete the associations explicitly. This is because executeUpdate() wasn’t doing anything with the associations, which in our case where the attachments.

We found a suitable solution after going through Amit’s blog about loading proxy objects. What we did was something like this.

def sentMails = Mail.findAllByStatus("SENT")
sentMails*.discard() //detach all the objects from session
sentMails.each{
       Mail proxySentMailObject = Mail.load(it.id) //load proxy object
       proxySentMailObject.delete() //using the proxy object to delete from DB
}

This also ensures that we don’t face the ConcurrentModificationException we would have faced if we tried deleting directly using

sentMails.each{
       it.delete()
}

Special Thanks to Amit for the wonderful post which was of great help!

Hope this helps
Vivek
vivek[at]intelligrape.com

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

  • Share/Bookmark
Tags: , , ,
Posted in Database, Grails
4 Comments »

How to Map database table without id with grails domain

Posted by Uday Pratap Singh on 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
Tags: , ,
Posted in Database, GORM, Grails
1 Comment »

MySql : Publish results of a query to a text file

Posted by Himanshu Seth on August 16th, 2010

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
No Comments »
« Older Entries
Newer Entries »