useful mysql commands and snippets

For most of my toy projects i work with mysql. It is easy to setup on windows and came bundled with my mac OS X iBook. There are few tasks that I keep doing time and again. Like creating users , doing a mysql dump and analyzing the tables. First let me list the often-repeated tasks

• How to create a mysql database
• How to create a mysql database user
• How to load a SQL file in MySQL Database(basically a batch file)
• How to dump mysql database using mysqldump
• How to dump mysql schema
• How to dump mysql tables data
• How to load data from previous mysql dump
• How to analyze mysql tables

Create MySQL Database

 
$mysql/> create database gloo;
 

Create MySQL local database user

 
mysql> grant all privileges on gloo.*
-> to 'snoopy'@'localhost' identified by 'peanuts'
-> with grant option
-> ;
Query OK, 0 rows affected (0.00 sec)
 

Create MySQL remote database user

 
mysql> grant all privileges on gloo.*
-> to 'snoopy'@'%' identified by 'peanuts'
-> with grant option
-> ;
Query OK, 0 rows affected (0.00 sec)
 

Load a SQL file into MySQL database

 
$mysql -u gloo -p < /usr/rjha94/schema.sql
 

Dump MySQL database using mysqldump utility 

 
syntax is mysqldump -u {user} -p {database} > {file_name}
$ mysqldump -u snoopy -p gloo > gloo.full.sql
 

Dump MySQL schema

 
(useful for creating schema across sites)
$mysqldump --no-data --add-drop-table -u snoopy -p gloo > gloo.schema.sql
[ This will not load the row information. ]
 

Dump MySQL database tables

 
$mysqldump --no-create-info -u snoopy -p gloo > gloo.data.sql
 

Load data from a dump file into MySQL

 
$ mysql -u snoopy -p < gloo.sql
 

Google satellite map of bangalore

Today i spent some time working with Google maps. I do not know if Google or yahoo has map data for Bangalore. so i am working with satellite maps. Meanwhile i will continue the hunt for map data also. I am more interested in showing hot-spots within a radius of x miles when the map is centered on some well known location. so far i have managed to do the following

• get the clicked point's lat and lon data
• tool tip for the place marks
• loading place marks on demand with the server supplied JSON data
• change center of map based on what well-known point the user wants
• place zoom and drag controls
• mouse click handler for markers and map
  

I have not yet solved the show hot spots within a radius problem. I will plug that tomorrow. I think i have the basic ingredients now. sprinkle some ajax dust and soon i will have my small app ready ;o) Biggest headache now is to get the lat and lon data of well-known places in bangalore. I can do it manually using google earth but that would take a lot of time. I will post the code tomorrow or whenever i manage to finish.

select boxes with rails

This evening i started out making select boxes in rails. Data for first select box is coming directly from a table. it lists down all the options and includes a blank also. Rails has this convention of rendering param names as x[y] and you can access them in controller code using params[:x][:y]

<%=
@locations = Location.find(:all , :order=> "name")
collection_select(:location, :name, @locations, :name, :name,{ :include_blank => true }) %>

The name of select box would be location[name]. In some cases though we want the _TYPES_ coupled tightly with our model classes. in such cases , you can define options inside a class, like.

payment_types = [ ["check","check"], ["credit card" ,"cc"]].freeze

I found this on appendix c of agile web development with rails. To use this array we have to include

<%=
options = [ ["select a payment option", ""]] + Order::PAYMENT_TYPES
select ("order","pay_type",options, {:include_blank=>true}) %>

vim file backup location on windows

I use vim on windows. When I open a save a file with vim, it always creates a backup file with tilda (~) appended to the file name in same folder. Now this looks plain ugly and second thing is, I want to centralize my vim backup location. You need to overwrite the default behavior of your vim editor.
 my _vimrc is sourced from these 2 files

• source $VIMRUNTIME/vimrc_example.vim
• source $VIMRUNTIME/mswin.vim

So I just go ahead and put the following two lines in my vimrc_example.vim file. End of all problems and happy vimming ;o)

if has("vms")
 set nobackup " do not keep a backup file, use versions instead
else
 set backupdir=c:\logs\vim
 set backup " keep a backup file
endif
 

Mapping Hibernate many to many associations

Let me walk you through creating sample M N association with hibernate. M N associations require 2 entities table and one link table to map the association. The example I took is for groups and users. A group can have many users and one user can belong to many groups. Think of your workplace. User Rajeev Jha is part of HR group, La montage group and Serve Chilled Beer group. At the same time La Montage group has many other members including coolcat, fred and Wilma. The problem is to map this association using hibernate.

create table contact (

contact_id int(11) not null auto_increment ,
last_name varchar(50),
first_name varchar(50),
primary key (contact_id)) engine=innodb ;

create table my_user (
user_id int(11) not null auto_increment ,
last_name varchar(50),
first_name varchar(50),
primary key (user_id)) engine=innodb ;

create table my_group (
group_id int(11) not null auto_increment ,
name varchar(50),
primary key (group_id)) engine=innodb ;

create table user_group(
user_group_id int(11) not null auto_increment ,
group_id int(11) not null ,
user_id int(11) not null ,
primary key (user_group_id)) engine=innodb ;

user group is the link table. Now we want to see the mapping files for user and group classes. There is no need for a separate mapping file in case of mysql if you are using the auto_increment primary keys. But when I map to oracle , i usually keep a mapping file for link table also. Just map the link table primary key column. The mappings for groups follow in User.hbm.xml. This is the inverse end of relationship. I have omitted other property mappings for brevity sake.

<set name="groups" table="user_group" inverse="true" lazy="true" cascade="save-update">
<key column="user_id"/>
<many-to-many class="com.agiti.hib.Group" column="group_id" />
</set>

And this is the mapping for Group table, that contains users , the inverse end of the relationship inside Group.hbm.xml . I just followed along the hibernate bible . idbag is require to generate the PK for the link table.

<idbag name="users" table="user_group" lazy="true" cascade="save-update" >
<collection-id column="user_group_ID" type="java.lang.Long">
<generator class="increment"/>
</collection-id>
<key column="group_id"/>
<many-to-many column="user_id" class="com.agiti.hib.User"/>

After you are through with the mappings, create or reverse engineer the POJO classes. inside Group.java, the relevant bits are :

public class Group{

private Long id ;
private String name ;
private List users = new ArrayList();

public List getUsers(){ return this.users ;}
public void setUsers(List users){ this.users = users ;}
// other details nuked
public void addUser(User user){
this.users.add(user);
user.getGroups().add(this);
}
}

You can write User.java on the same lines. After that it is just a matter of using your POJO classes.

head rush , wireless and frustrations

Frustration is creeping on me , from everywhere. Past 2 days i have not done much @ workplace. And i left gloo work midway after struggling with the layouts for 2 hours. whoever said making web forms is easy should be hanged and i want to be the hangman. Maybe time for a break, past 5 weeks have been truly 7 day weeks. in between i got two new books. head rush ajax and Agile ROR book. I kind of like the head series with all the pretty pictures though content wise it is not much! Only good thing to happen was the new shiny netgear wireless router. Now I and chota can share our internet connection. Maybe time to put the frustrations away and take a clean break for 2 days this weekend.

Decompile JAVA classes in vim

Vim has a cool jad plugin now. What that means is you can see the decompiled .class files right inside your vim editor. Just load any class file in vim editor and you can see the decompiled version. This is the http://www.vim.org/scripts/script.php?script_id=446  vim plugin and you can download jad from this location http://www.kpdus.com/jad.html  . Just copy the vim script to vim plugin folder and put jad in your path. Happy viming ;o)

creating your own perl module

we are going yo create a simple perl module.  Good thing is there are tools shipped with perl that generate stub code. Lets say we want to generate module called Gloo::Store. To generate the stub issue the command $h2xs -AXc -n Gloo::Store. This would create a folder called Gloo-Store. Your module Store.pm would be located in Gloo-Store\\lib\\Gloo folder. I will skip over lot of details.

The intention is to just add two methods to our module that we can use in our other scripts.  we have added a method to this module called save that uses CGI::Session module to store data. The extra code we wrote is in color red. The stub generated code is in color black.  You can have your documentation right inside the module. All the documentation is in color blue. And finally we do not export any method names because we are using the OO interface.

package Gloo::Store;
use 5.008007;
use strict;
use warnings;
use CGI ;
use CGI::Session ;
require Exporter;

our @ISA = qw(Exporter);
our %EXPORT_TAGS = ( 'all' => [ qw( ) ] );
our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );
our @EXPORT = qw();
our $VERSION = '0.01';

sub new {
  my $package = shift;
  return bless({}, $package);
}

sub save {
    my $shelf = shift ;
    my ($cgi, $token, $value) = @_ ;
    if( !defined $token || $token eq "" || !defined $value || $value eq "") {
        return ;
    }
    print " $token :: $value \n " ;
    # @todo - change this location
    my $session = new CGI::Session(undef, $cgi,{Directory=>"c:/rajeev/perl/tmp"});
    $session->expire('+15m');
    my $sid = $session->id();
    # put data in  session
    $session->param($token, $value);
    return $sid ;
}

# Preloaded methods go here.
1;

__END__

=head1 NAME
Gloo::Store - Module for storing user preferences of Gloo website
=head1 SYNOPSIS
  use Gloo::Store;
  $gloo = new Gloo::Store ;
  $gloo->save('token','value');
=head1 DESCRIPTION
GlooStore is used to store the gloo user preferences in cgi sessions. The GlooStore module
uses cgi::session module. We require the user to have cookies enabled in their browser. And
no, no cookie monster will eat your computer because we just store the CGI session id in cookies.
=head2 EXPORT
None by default.
=head1 SEE ALSO
CGI::Session
=head1 Methods
=over
=item  save($cgi_handle,$name,$value)
 method to save name value pairs in gloo store.

=cut

=back
=head1 AUTHOR
rajeev jha, E jha.rajeev@gmail.com E
=head1 COPYRIGHT AND LICENSE
Copyright (C) 2006 by rajeev jha

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.8.7 or,
at your option, any later version of Perl 5 you may have available.
=cut

The usage of this module has been included in the documentation itself. To install this module, go inside the Gloo-Store folder and issue
$perl Makefile.pl
$nmake
$nmake install

Our module would be installed to perl site/lib folder.

powered by performancing firefox

Ruby on Rails Graffiti

This weekend i am going through Agile web development with rails. After 2 marathon sessions i have managed to skim over most of the books. Now i am realizing the side effects of speed reading, so many words dancing in my head

Finishing the UI takes a long time

Things look simple when you get the wireframes. backend coding also does not take much time. But when it comes to UI there is no end in sight. Creating a polished UI takes a lot of time. The kind of things that you need to do

• Error validations. Empty fields, numeric validation, email format etc. I personally do not like doing validations in server scripts only. it is double the work if you want to do validations in javascripts as well as server scripts.
• Make sure every font and font size is perfect.
  
• All the navigation links should work  otherwise integration with the rest of the system would break.
  
• Complex UI would have role based rendering. So you need show different UI to different type of users
  
• Correct handling of data returned from server.
  

It can be frustating when you work on your UI for 3 days and people come back with hundreds of comments. But then that is part and parcel of developing an application.

powered by performancing firefox

Passing UI filters as json to cgi scripts

We have a search page that allows the users to define custom filters through UI. One way to pass filter information to server side script is through hidden fields. However if the number of filters increase then this scheme can be a headache. For n filters you would need N input fields. what if we have a filter object in our javascript code ? How about taking all the input from user, creating a filter javascript object and passing that object to the server side script ?
Sounds nice ? All the house keeping can then be moved to OO style javascript.

Filter UI can be defined inside a standard HTML form.
<form name="filterForm" id="filterForm" method="post" action="/cgi-bin/store.pl">
name

other blah blah ..........
</form>

when the Filter form is submitted, a createFilter javascript method is called. This is not OO style but I would suggest doing that.

<script type="text/javascript" ="" src="/app/js/json.js"> </script>

<script>
filterObj = {};
function submitForm(){
var filterForm = document.getElementById('filterForm');
var name = filterForm.name.value ;
blah blah .....
filterObj.name = name ; filterObj.age = age ; filterObj.sex = sex ;
filterForm.payload.value = filterObj.toJSONString() ;
filterForm.submit(); }
</script>

I got json.js library from www.json.org . This makes javascript manipulation easier. so just go and grab a copy. one caveat , GET was not submitting the json string for me, it was submitting individual members of the json object . I am using apache 2.2.x. Now, on the server side. you just extract the json string from request parameter and convert the json string to perl object using cpan JSON module.

use JSON ;
my $json = new JSON ;
my $js = my $jsObj = $json->jsonToObj($cgi->param('payload'));
# print members of this object
print " \n $jsObj->{'name'} :: $jsObj->{'age'} :: $jsObj->{'sex'} \n" ;
thats it folks!

Hibernate and oracle virtual private database

Separate data of user A from user B is a must-have of online hosted application in today's world. using separate schemas or database do not make much sense because everyone is using the same application . Point is , why do all the maintenance ?

Everywhere I looked, people just advise against this idea of multiple databases or multiple schemas citing performance reasons. same datamodel, same database. But you can not just let people query the base tables holding everyone's data. we need some form of security. The concept that provided security in a multi tenant database is called virtual private database or VPD.

Google for oracle row level access, oracle FGAC, sys_context and oracle VPD. One question to ask is , why not do this in web server layer (middleware). We can fetch result sets from database and filter it. YMMV but we wanted to do it at database level. our requirements are simple, so we do not want to use FGAC etc. Our scheme is to stripe base tables on a client_id. Then we create views on base table with a predicate

FROM BASE TABLE WHERE CLIENT_ID = GET_CLIENT_ID FROM CONTEXT

We do not provide select access on base tables, you can only query the views on base tables. Before doing any select queries , you need to set the client_id in sys context by executing a stored procedure. That is essentially how it works with Oracle.

Next question to ask is  how do we use this scheme with hibernate ? One idea people come up with is that get connection from hibernate session and then execute stored procedure on this connection. You work with your hibernate session after that. Your client_id would then already be set. This looks like lot of work and discipline. Doing something every time you get a hibernate session may not be the best solution.

so is there a better way? YES. we can provide a custom connectionProvider to hibernate. we use C3P0ConnectionProvider. So we decided to write our custom connection provider by extending C3P0ConnectionProvider and pluggin in required logic.

we override just one method of Connectionprovider interface called getConnection()

VPDConnectionProvide extends C3P0ConnectionProvider{
   getConnection(){
      Connection conn = super.getConnection();
      // execute stored procedure to set client_id in sys context for session
      return conn ;
   } 
}

 

Now whenever hibernate requests a connection from ConnectionProvider ,the stored procedure is executed. Use hibernate mapping over this t_view. Thats it!

see the following resources also

• VPD thread on hibernate forum
• VPD article on OTN
• Information on oracle security
• Tom kyte's answer to Design issue - Multiple schemas vs. additional fields