Issues while migrating MySQL to Postgres database

Like everyone else, I started to see the disadvantages in MySQL. Lack of ACID support, no analytical functions, no strict ANSI SQL compliance, no full text search etc.,

Following are the things to take care when you have plans to migrate your data to Postgres from MySQL

  1. Column names with mixed lower and upper cases
  2. Limit Clause SYNTAX change
  3. DATE_FORMAT function
  4. Convert TINYINT(1) to BOOLEAN datatypes
  5. Division by 0
  6.  null value in column “<column_name>” violates not-null constraint
  7. PG::CharacterNotInRepertoire: ERROR:  invalid byte sequence for encoding “UTF8″: 0xe92042 (Sequel::DatabaseError)
  8. Escape single quotes – :%s/\\/’/g

Ignore/Disable Crashlytics reports during App Development

In your ApplicationController (or the class that extends Application),

@Override
public void onCreate() {
Crashlytics crashlytics = new Crashlytics.Builder().disabled(BuildConfig.DEBUG).build();
Fabric.with(this, crashlytics);
}

Gradle will generate the appropriate value for BuildConfig.DEBUG based on your current build.

Editing /etc/hosts file in Genymotion Emulator

I use my local Rails app while developing the Android app. When I tried to connect “http://localhost:3000″ using Volley from the Android app that runs under Genymotion, it threw timeout error. It is because Genymotion runs under Virtualbox and Mac is it’s host OS. To get the IP of your host machine:

☁ duggout [master] ⚡ ifconfig vboxnet0
vboxnet0: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> mtu 1500
ether 0a:00:27:00:00:00
inet 192.168.56.1 netmask 0xffffff00 broadcast 192.168.56.255

Now you can use “http://192.168.56.1:3000″ in your Android app to connect to the localhost of your host OS.

If you need to work with subdomains (mostly required for API driven applications) where your application requires the URL to be of the form “http://api.example.com”, you need to edit the hosts file of the virtualbox OS.

Connect your Genymotion device with adb.

./adb devices

to see your Genymotion device. If you do not see your device, open Genymotion settings, goto ADB tab, choose your ADB location instead of the default ADB provided by Genymotion. Restart emulator.

Create a new file (/tmp/hosts) in your system with the following contents:

192.168.56.1 localhost api.localhost.com

Now you need to push this file to the Genymotion Virtualbox OS using adb.

./adb root

./adb remount

./adb push /tmp/hosts /system/etc/

Restart emulator. Now you can connect using both localhost and api.localhost.com from your Android code.

Include JST EJS Partials in Rails Backbone

Template File

<backbone/templates/template.js.jst.ejs>

This is a template file.
<%= JST['backbone/templates/_some_partial']() %>

Partial file

<backbone/templates/_some_partial.js.jst.ejs>

This is a partial.

Final Output when you render the template:

This is a template file.
This is a partial.

Refer this issue in GitHub.

Verify Discourse with Google Webmaster with HTML File Upload method

My DuggOut Forum is hosted on a subdomain. Though I have verified the parent domain (duggout.com) with Google Webmaster already, it still asked me to verify this subdomain.

Since all the other methods (Google Analytics, CNAME, TXT record, meta tag) failed, I had to go with HTML file upload based verification.

1. SSH into your host machine

2. SSH into the docker container

cd /var/discourse (or wherever discourse is installed)

./launcher ssh app

3. Create the Google provided HTML file under /var/www/discourse/public directory

Now, go and hit the URL –

http://forum.yoursite.com/google-verification-****.html

You will see it working and your Webmaster verification is complete.

Advanced queries using ActiveRecord in Rails

1. Replace IN Clause with Sub Query

While writing a strategy for my Cricket Stats Search engine, I had a scenario like this:

I need to generate the list of match innings that belongs to a particular set of matches (say, matches starting on a particular date).

So,

matches = Match.where(start_date: '2014-02-18')

Then I wanted to use this in an IN clause while fetching matches innings. So I went ahead and wrote a ActiveRecord query like this:

MatchInning.where(match_id: matches.pluck(&:id))

This would generate the following SQL:

SELECT * FROM matches_innings WHERE match_id IN (1,2,3,4,5,6....10)

This query is highly inefficient as this runs 1 query to fetch matches and then another one with the list of values in IN clause. The SQL looks bad, and won’t work the number of values in matches is huge.

To fix this, I changed the query like this:

MatchInning.where(match: matches)

This would be the SQL:

SELECT * FROM matches_innings WHERE match_id IN (SELECT id FROM matches WHERE start_date = '2014-02-18')

For this to work, you should have set up the associations correctly. MatchInning should belongs_to match

PostgreSQL – Copy To and Copy From using Select queries

When you need to copy a small set of data from one database to an another (say, from dev system to a QA system), you could use the copy feature of PSQL.

To get the data in CSV of a select query:

duggout=> copy (select * from lookups where lkp_type = 'homepage_items' ) to STDOUT with csv;
6514,homepage_items,side_1,6,,,,,,,,,2014-10-06 15:47:25.216133,2014-10-06 15:47:25.216133
6515,homepage_items,list_main,"1,2,3,4,5,6,7",,,,,,,,,2014-10-08 02:52:52.995771,2014-10-08 02:52:52.995771
6516,homepage_items,image_main,"5,6,7",international_match,f,,,,,,,2014-10-05 14:06:55.591311,2014-10-11 08:14:47.196494

To load the data into the table in another environment:

COPY lookups FROM STDIN with csv;

Now, paste the CSV data with a trailing new line. And then \. (backslash with a dot). This should append the CSV data in the target table. When you want to copy more data, use the file option instead of STDIN/STDOUT. Check the doc for the usages.

To copy a large table, use the binary option:

remote_db=# copy table_name to '/tmp/a_path' with binary;
COPY 38765

You could copy the file to your local using scp, if the file is in a remote server. Then,

local_db=# copy table_name from 'local_path' with binary;
COPY 38765

It is as simple as that!

Rails – Redirect for incorrect URL slug, like StackOverflow

When you visit a StackOverflow question with a different URL slug, they redirect you to the right URL with the correct slug. This is important for two reasons:

  1. When the title of the question changes, the URL with the old title will be obsolete
  2. Keeps a single Permalink for every question

In your controller:

before_action :redirect_if_incorrect_url_code, only: [:show]

private
def redirect_if_incorrect_url_code
return if @model.url_code == params[:url_code]
redirect_to @model.route_param
end

In your routes:

get 'models/:id(/:url_code)', to: 'models#show', constraints: {url_code: /[a-z\-]+/, id: /\d+/}, as: :model

In your model:


def route_param
[id: id, url_code: url_code][0]
end

Showing a spinner/loading cursor icon for Rails 4 Turbolink

In Rails 4, Turbolinks loads pages using AJAX. It fetches only the body content of the new page and updates the current page with the new content and the title as well. This avoids reloading of all the assets such as Javascript and CSS. This has one small drawback though. The user might not really know if the page is loading or not, since there are no indication in the tab. To fix this, we can update the cursor of the page to a loading cursor whenever turbolinks starts to load a page. Create a new coffee script file with the following content, and load it after the turbolinks is loaded.


$ =>
startSpinner = ->
  $("html").css "cursor", "progress"
  return
stopSpinner = ->
  $("html").css "cursor", "auto"
  return
$(document).on "page:fetch", startSpinner
$(document).on "page:receive", stopSpinner

The following code lets you trigger a page tracking for Piwik:


$(document).on 'page:change', ->
if window._gaq?
_gaq.push ['_trackPageview']
else if window.pageTracker?
pageTracker._trackPageview()
return