MySQL GROUP_CONCAT function

I knew about this MySQL function called "GROUP_CONCAT" lately and I think it is really a very useful function to use

To make simple for you to understand, I will use a small example to illustrate on

Consider we have a table called listings which has many to many relationship with another table called geographies knowing that the join table name is listings_geographies

Now look at this query

select listings.id, listings.display_name, geographies.name as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

In my example I have only two listings: the first has 3 geographies and the second has 2 geographies. By running this command, you will get 2 records but the field called "geography_name" will have one of the three values and not all the values

What if you want to grab all the three values and add them in 1 field so that you get 2 records but with "geography_name" field containing all geographies names values. If you want that, then you can rely on this nice function "GROUP_CONCAT"

The above function can be re-written to be this one

select listings.id, listings.display_name, GROUP_CONCAT(geographies.name SEPARATOR ' ') as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

That's it
I needed this function as I was using Sphinx and I wanted to have a query written to grab all record info with its associations in 1 record in order to be indexed

Enjoy! :)

“Distinct vs Group By” SQL Talk

I am writing this blog entry to tell people about main differences between these two instructions because any misunderstanding to the true difference between them can cause big problems

That what happened to me actually
I didn’t not know the true difference and as always I have chosen the easy way and rely on Distinct instead of Group By

Let me show an example which I was working on
and that one really helped me understand the difference between these two

Suppose you have a Deal which is associated with several geographies
Each geography has a level representing its level in the tree of geographies. The higher the level, the lower the geography exists in the tree.

The objective was to get all deals in several geographies which I was given their IDS and sort them by depth/level from lowest to highest

I started writing my query to be like that
which is wrong by the way

select Distinct(deals.id) from deals INNER JOIN deals_geographies ON deals.id = deals_geographies.deal_id INNER JOIN geographies ON geographies.id = deals_geographies.geography_id WHERE geographies.id IN (id1, id2, id3) ORDER BY geographies.level DESC

it sounds good at the beginning. If you see it right then you are facing the same problem I was facing before. So please continue reading

The problem here is that inner joining will perform a Cartesian product between deals and geographies and as we have several geographies for each deal, we will get several rows for each deal

When you say DISTINCT without defining the criteria to the DB engine, it will choose any row which could get you the highest geography this deal has or it can get you the lowest geography this deal has

This is an Ambiguous Selection
but it is your fault that you did it that way

Lets see now how group by will solve this

select deals.*, MAX(geographies.level) as max_level from deals INNER JOIN deals_geographies ON deals.id = deals_geographies.deal_id INNER JOIN geographies ON geographies.id = deals_geographies.geography_id WHERE geographies.id IN (id1, id2, id3) ORDER BY max_level DESC group by deals.id

Now I told the DB engine what should the criteria it should use to remove multiple deal rows and which row it should leave which I wanted it to be the one with the largest geography level as this is my desired condition

My last conclusion is that you can use DISTINCT in one of these cases only

1. you have a 1 to 1 relation between two or several tables and thus no ambiguity will be there
2. you only care about 1 table fields and you are just using the other table for no more but Filtration which was not the case above as I needed it for ordering

btw, there is a rumor I heard saying that DISTINCT is not standard SQL. Not sure of this info but you can check that yourself and maybe comment and tell me

Hope this blog was useful and enlightening :)

Useful RVM resources

I think these few resources are useful for any RVM newbie as me :)

Read them in that order

• http://rvm.beginrescueend.com/rvm/basics/
• http://rvm.beginrescueend.com/rubies/default/
• http://rvm.beginrescueend.com/gemsets/basics/
• http://rvm.beginrescueend.com/rvm/best-practices/

For me, I made use of the first and third links although I see all of them useful but these ones helped me install and use several ruby versions with several gemsets which is why I used RVM at first place

Hope I was useful
and happy RVM journey

Installing RVM System Wide

Today I got introduced to one very nice solution developed by Wayne E. Seguin. It is called RVM and it is recommended for ruby developers working on Debian machines.

What is RVM ?

Simply RVM allows users to install multiple ruby versions and switch between them easily. It also allows having multiple sets of gems for different projects easily.

Installation Steps

Before getting into details, I would like to say that any steps mentioned here are grabbed from these two links. I only collected parts from them and added them in an easy way

http://rvm.beginrescueend.com/rvm/install/
http://rvm.beginrescueend.com/deployment/system-wide/
http://rvm.beginrescueend.com/rubies/installing/

1. from your linux console run
2. bash < <( curl -L http://bit.ly/rvm-install-system-wide )
3. you now have rvm command installed here at /usr/local/lib/rvm
4. After installation you should add this line below to your profile
5. [[ -s "/usr/local/lib/rvm" ]] && . "/usr/local/lib/rvm"
6. To do so, i added the line above at the end of the /etc/profile file. But, you can add it in other places according to your needs. Read more about profiles here
7. then run this one
8. source /usr/local/lib/rvm

Now you are ready to install any versions of Ruby and Ruby Enterprise versions available

For me, I installed Ruby 1.8.7 and its enterprise version this way

1. rvm install 1.8.7
2. rvm install ree-1.8.7

That’s it
Hope you enjoy RVM as I hope I enjoy it as well

Forget root password of a Debian Machine & resetting it

I am writing this small post just because

• i forgot the root password of my debian machine
• didn’t find a through post that list all steps in one place and had to look at several ones at a time

The steps are as follows

I am quoting these lines from that post

Some Linux distribution, such as Ubuntu for instance, offer a specific boot menu entry where it is stated "Recovery Mode" or "Single-User Mode". If this is your case, selecting this menu entry will boot your machine into single user mode, you can carry on with the next part. If not, you might want to read this part.

Using GRUB, you can manually edit the proposed menu entry at boot time. To do so, when GRUB is presenting the menu list (you might need to press ESC first), follow those instructions:

• use the arrows to select the boot entry you want to modify.
• press e to edit the entry
• use the arrows to go to kernel line
• press e to edit this entry
• at the end of the line add the word single
• press ESC to go back to the parent menu
• press b to boot this kernel

The kernel should be booting as usual (except for the graphical splash screen you might be used to), and you will finally get a root prompt (sh#).

Here we are, we have gained root access to the filesystem, let's finally change the password.

According to the above words, we should be ok and we have access to the file system. At this state you run the command “passwd” and enter the new password.

If it worked with you then Thanks to the editor. If you got some problems like me, then keep reading.

Problem 1

After editing grub line and add the “single” keyword at the end of that line. I got it loading well until i was prompted for the root password for maintenance and give the ability to skip but by then i will be leaving runlevel 1 and entering runlevel 2 getting login/password prompt i am trying to skip.

To solve this problem do the following:

• Edit the grub line again and leave the keyword “single” there as before but add at the end as well these words “init=/bin/bash”
• exit edit mode
• press the button “b” while you have this modified grub line highlighted to start booting with this modified grub line

Voila, you have now access to shell as a root. run the command “passwd” and you should be fine entering the new desired password. If you got a problem, then continue reading.

Problem 2

Whenever i run the command “passwd” and re-enter the new password i get this error at the end

authentication token lock busy

If so, know that the problem is that you are accessing the system in read-only mode. In order to access it in read-write mode, do the following.

From the shell run this command

mount -o remount,rw /

after that run “passwd” command and this time you should have the ability to enter the new password

and live happily ever after

Factory Girl in Development

Some many developers use Factory Girl as a replacement to Fixtures and they depend on this gem in Test cases writing.

But Factory Girl can be very useful also in development and can be used in order to generate a bunch of dummy data with different specs for showing your work and reviewing all its details.

The problem we face is that Factory Girl can generate more records in tables that should have their data unchanged such as Countries table.

For example:

Factory.define :user do |f|
  f.association :country
end

Factory.define :job do |f|
  …
  f.association :user
end

Now, when we run this piece of code that generates 10 jobs

10.times { Factory.create(:job) }

we will get 10 countries auto-generated
violating the rule we wish to maintain which is having the countries table as it is

To workaround this problem without causing any changes in the code written previously, i came up with that solution

class Factory
  class << self
    alias_method :create_original, :create
    
    def create(name, overrides = {})
      if name.to_s == 'country'
        country = Country.first
        return country if country
      end

      create_original(name, overrides)
    end
  end
end

The logic introduced in the above script is simply adding a layer before creation that checks if the created object of certain type and if that type is desired to not be generated, we return the first entry we have in the DB else we do the normal creation

You can add this script in a file and load it in the development environment and use Factory Girl safely without fearing of generating data for tables that should remain as lookup tables

Enjoy :)

Factory Girl & Polymorphic Associations

Suppose you have a case like that

class Address < ActiveRecord::Base
  belongs_to :addressable, polymorphic => true
end

class Listing < ActiveRecord::Base
  has_one :address, as => :addressable
end

class Customer < ActiveRecord::Base
  has_one :address, as => :addressable
end

Now in your factories you will have something like that

Factory.define :listing do |f|
  f.association :address
end

since addressable is required, we choose it to be by default related to a customer unless else stated

Factory.define :address do |f|
  f.association :addressable, :factory => :customer
end

right now if you tried to use the Listing Factory, you will get an address associated with dummy customer and no effect on your side

How can this be fixed ?

Factory.define :listing do |f|
  f.after_build do |listing|
    listing.address = Factory.create(:address, :addressable => listing)
  end
end

This was the only solution i found after searching for a while
which is a good solution and doesn't need a lot of work to be done

sanitize gem issue

I have used ‘sanitize’ gem to remove HTML tags from entered text
the version i was using was ‘1.0.8’

After using it for a while, i found an issue in it where calling
Sanitize.clean(“’”) will return “#39;” while quote isn’t HTML character

Looking around for a reason for this issue, i found that this is a defect that was detected in older versions and that it is now fixed in version ‘1.1.0’

So anyone who has this issue can simply remove his installed gem and install the latest one

smtp 555 5.5.2 Syntax error (Net::SMTPFatalError)

This title was part of an error message i got while dealing with action_mailer

i looked everywhere for a reason for this error message and found so many reasons but none of them matched with my case

So i liked to share why i got this error. Simply because i was adding no recipients to my email. I know it is very weird to send an email without having a recipient but getting this error message i much more weird and strange

/usr/lib/ruby/1.8/net/smtp.rb:930:in `check_response': 555 5.5.2 Syntax error. 24sm123817eyx.21 (Net::SMTPFatalError)
        from /usr/lib/ruby/1.8/net/smtp.rb:899:in `getok'
        from /usr/lib/ruby/1.8/net/smtp.rb:842:in `rcptto'
        from /usr/lib/ruby/1.8/net/smtp.rb:834:in `rcptto_list'
        from /usr/lib/ruby/1.8/net/smtp.rb:833:in `each'
        from /usr/lib/ruby/1.8/net/smtp.rb:833:in `rcptto_list'
        from /usr/lib/ruby/1.8/net/smtp.rb:654:in `sendmail'
        from /usr/lib/ruby/gems/1.8/gems/actionmailer-2.3.2/lib/action_mailer/base.rb:683:in `perform_delivery_smtp'
        from /usr/lib/ruby/1.8/net/smtp.rb:526:in `start'
        from /usr/lib/ruby/gems/1.8/gems/actionmailer-2.3.2/lib/action_mailer/base.rb:681:in `perform_delivery_smtp'
        from /usr/lib/ruby/gems/1.8/gems/actionmailer-2.3.2/lib/action_mailer/base.rb:523:in `__send__'
        from /usr/lib/ruby/gems/1.8/gems/actionmailer-2.3.2/lib/action_mailer/base.rb:523:in `deliver!'
        from /usr/lib/ruby/gems/1.8/gems/actionmailer-2.3.2/lib/action_mailer/base.rb:395:in `method_missing'
        from test_sending_emails_in_ruby.rb:31 

this is the error message i got and it was solved when i added a recipient

Memory Leakage while using Mechanize

I was working on a task that scrape several web pages. After running this task for a while, i found that memory taken by my process is raising forever until it was about to eat all memory available of the server.

after some investigation regarding this matter, i knew that the problem was in my understanding to how mechanize agent works

let me explain with an example

agent = WWW::Mechanize.new
while(true)
page = agent.get(“www.example.com”)
end

in this example, memory will be consumed because mechanize keeps history within the agent, i looked in its documentation and found that there is a parameter which is called “max_history” which when set will fix this issue i think but didn’t try

also a fix to such issue, if you don’t need history is to write your code like that

while(true)
agent = WWW::Mechanize.new
page = agent.get(“www.example.com”)
end

That’s it, maybe this piece of information can be useful for someone facing this issue just like me

Install Mechanize On Debian

Installing mechanize gem on Debian should be as easy as running this command

gem install mechanize

but this won’t succeed unless you install these packages on your Debian machine

apt-get install libxml-dev libxslt1-dev

Once installed, gem will be installed seamlessly

Update:

if the above apt-get command didn't work with you and you got an error that package doesn't exist

try this new line

apt-get install libxml2-dev libxslt1-dev

as some packages names has changed

[Linux] Mounting windows folders

Due to my new configuration which is using Windows as my default OS and Debian shell through Virtualbox, my need to have a folder shared between these two environment become a must in order to ease file sharing and exchange.

At first, i depended on “shared folders” feature of VirtualBox which after a while failed as i always get a “Protocol Error” whenever i deal with files IO on that shared folder.

That’s why i looked for another solution that is more stable than that one and reached to using “CIFS” as a way to mount windows shared folder on my Debian machine.

The steps are very easy to be made and gives you a stable robust solution away from VirtualBox problems. i can summarize these steps as follows

1. install on your Debian machine “smbfs” package which will add this new type of mounting called “CIFS”
2. suppose your machine IP is “192.168.1.6” and you shared a folder on it called “work” and this folder is secured to be used only with certain group which is administrators and one of these administrators is named “BioNuc” and have a certain password then your command will be                                                                                         mount -t cifs -o username=BioNuc ‘\\192.168.1.6\work’ <linux-path-to-mount-on-it>
3. After writing this line, you will be asked to insert your password in order to make mount process successful

That’s it, Enjoy sharing folders seamlessly

Giants Alliance

Two giants are there one called Windows and another called Linux. As i am fan of Windows Interfaces and Usability (by the way i use Windows XP) and also fan of the extreme shell power of Linux, I wished if i can exploit these two powers at the same time.

The solution i found was using Windows XP as my primary OS and adding Debian version on virtual machine. You may be asking why should this solution be said when it is well known and made by some many users but actually, i made some modifications to this solution.

What i did can be summarized in these following points:

• installing debian without any interface at all, just a shell no more
• using virtual machine shared folder technique to share a folder between my XP machine and my Linux machine
• installed ZOC program which is a powerful shell program that allows connecting using openssh and supports multi tabs which is very important feature not available in putty
• installed openssh-server on debian
• fixed an IP for my debian machine so as to save my openssh configuration one time only without changing it each time i connect to it

This way, i enjoy my XP machine and whenever i need the shell of linux. i connect using openssh on my debian virtualized machine and do what i want

I use this solution while developing ROR applications, i prepared the environment i use as follows

• installed ruby, rails, mysql on my debian machine
• opened remote connections to mysql database so as to use MySQL Query Browser from XP machine

Now, i open my IDE on XP machine that changes in folders place in the shared folder between two machines and i open my server from virtual machine and test apps on debian machine IP rather than my localhost

This experience is great and i enjoy it so much as i feel i have the power of each environment Windows & Linux at the same time

In order to optimize the performance of my Virtual Machine somehow, i raised the priority of these virtualized processes to ‘High’ or even ‘Realtime’. it is now better but not perfect

I think in order to raise the performance significantly, i need to buy another motherboard supporting Intel VT technology. Using such technology i can move virtualization from software layer (being a process that Windows XP Scheduler deal with) to hardware layer which should improve performance as stated

That’s it, i suggest you try this experience and Enjoy as i do

Export Google Document To WIKI STYLE Document

the idea is simple and only needed is that you do some simple steps and you are done

follow the following steps:

• save google document as HTML file
• view html page source and extract the html inside document body tag
• use this great tool and add in it the html you copied and you will have the wiki style document generated

That’s it, you now have the document you wrote in WIKI style. Take this text and play with it as you wish

Restore Your Deleted Partition

Yesterday, i passed through a great panic after wrongly deleting my 3 partitions. Suddenly i felt that i lost all my data forever and there is no way out of such problem.

First how this problem occurred ?? actually this issue aroused from “computer management” program on my Windows XP. I opened it and choose one partition and right click and chosen delete. After confirmation, i found all 3 partitions were deleted. What a stupid application !!! i don’t know why should windows has an application that deal with such sensitive parts when not sure of its performance and reliability

Anyway, after some search on the internet, i found a great application called “TestDisk”. Anyone can download this free application from here

I did download it and followed this nice tutorial who showcase how this application works and how to use it safely. I did exactly as it stated and i restored my deleted partitions back again as before with nothing being lost

Thanks “TestDisk” for the great application and you really deserve a donation.

Gems vs Plugins

Which one to use GEMS or PLUGINS?
This was a question i always asked and i have searched on the internet for comparisons between each one and the other

i can see that people prefer gems for the sake that it is the new future, it has obligatory versions other than rails plugins

But as for me, i only see gems useful in these two cases

1. when using code provided as gem outside of Rails, maybe with some ruby application
2. if you want to share code written in gem among applications

other than that, i see plugins better and much more maintainable for each application on its own

Uploading Images from URL using Paperclip

I was searching for a way inside paperclip that uploads image from Url instead of File which is the normal behavior handled by Paperclip but I didn't find that

After too much search and trials, I reached to a solution that is easy to be implemented and handle all cases you can expect in this process

I am applying the solution inside my model but it is preferred to add a patch to Paperclip version to use so that the same logic be applied everywhere but this exercise is left for you to do

To check the code and how it evolved to handle all special cases, Please review my gist here

Cascaded HTML SELECT elements using JQuery V2

This is a small update i made to the code, This code fixes a bug detected and also get rid of some duplicated code

his is an update to the JS function

eSpace.HTML = {
cascadeLists: function(parentId, childId, callbackFunction){
$("body").append('');
var childOptions = $('#' + childId + ' option');
$('#' + parentId + childId).html(childOptions);

$('#' + parentId).change(function(){
var parent = $('#' + parentId)[0];

var selectedOptionValue = '';
if (parent.selectedIndex > -1)
selectedOptionValue = parent.options[parent.selectedIndex].value;

if (selectedOptionValue == '')
$('#' + childId).html($('#' + parentId + childId + ' option[value=""]').clone());
else {
var childs = $('#' + parentId + childId + ' option[parent="'+selectedOptionValue+'"]');
if(childs.size() == 0)
$('#' + childId).html($('#' + parentId + childId + ' option[value=""]').clone());
else
$('#' + childId).html($('#' + parentId + childId + ' option[parent="' + selectedOptionValue + '"]').clone());
}

$('#' + childId).trigger("change");

if(callbackFunction != null) callbackFunction(parent.options[parent.selectedIndex]);
});

$('#' + parentId).trigger("change");
}
}

GMarks vs Foxmarks

I have found some many friends of mine interested so much in GMarks extension to sync their bookmarks from one pc to another.

As i am also interested in bookmarks sync across computers, and as i also respect google extensions and was a frequent user of Google Browser Sync until they stopped supporting such extension. I said to myself that i have to see what this extension gives

Frankly speaking, using GMarks is very ugly, yes you bookmark and tag your bookmarks but the way of retrieving tagged bookmarks is very old fashioned. It takes us back to the era of Firefox 2 of adding bookmarks in folders and then whenever you look for something, you open your bookmarks and look for what you want. SO UGLY AND OLD FASHIONED way

To understand why GMarks is a bad choice, lets have a look at what Firefox 3 introduced which became a de facto and also is going to be there later on in other browsers even google chrome

With the arrival of Firefox3, we got rid completely of two main stupid things

• searching for bookmarks in folders, they introduced tags
• searching bookmarks then if not there, google using one of the search engines beside navigation bar

Firefox 3 focused on value, simply we should deal only with one entity whenever we open a new tab, this entity is the location bar

Since you write keywords on you search engine to get you results, and since now you tag your bookmarks with keywords then we all agree that it is only keywords that matter. The new scenario became open a new tab, you are already on the location bar, write the keywords you are searching for, Firefox will list you tagged bookmarks and some other results from history.

Satisified then fine take any like from this collection. if not satisified then simply hit the enter key and you will be taken to search results from the search engine you prefer

Very lovely way, focusing on one position and having everything in hand for you. let's see what this GMarks did for our lovely neat scenario

Now, if you want something, you go to Gmarks tab beside tools and you will find your tags, you search for the tag you want then you find your bookmarks there. Not satisfied then google and write again the same tags you were about to use. SIMPLE WASTING YOUR TIME AND DON'T EXPLOIT MULTIPLE TAGS SEARCH NOR HISTORY SEARCH

simply guys, it is very stupid. Please get rid of this ugly extension right now. you are brainwashed with it. Simply use Foxmarks

it don't interfere in your cycle at all. It just sync on shutdown or from time to time as you configure it and whenever you add it from any other browser on same pc or another pc you get your bookmarks ready for you

it also has this features

• compatabile on many browsers, i think safari and opera
• has web interface to use from anywhere
• sync passwords also encrypted if you wish to
• has a new fancy feature added which is suggesting tags from other people to you whenever you tag your bookmark

To be also honest, i must say that Foxmarks is great but you need to disable its sync from time to time and make it on shutdown because it has currently a bug that causes a slowdown to firefox on synchronizarion

i wish this post can help you getting rid of your slavery to GMarks. see the light with Foxmarks instead

Software Update Mess

Nowadays, every software application has its own update program that is run in the background waiting for any new update to occur and notify you with it.

I see this is leading to more processes being run without any reason and taking time on startup and even bandwidth and cpu clocks

I got rid of all these after using
Filehippo software update program

this program is very nice one, it do this update idea but all these update programs are now in one place only, i stopped firefox update, apple update and so many and whenever something new exists this update program just tell me

I suggest it for anyone who hated such update programs, one for java, one for adobe reader, another for open office and so many others

Hope this may be useful small tip for Windows users like me