Mysql change collation or character set

http://blog.arkency.com/2015/05/how-to-store-emoji-in-a-rails-app-with-a-mysql-database/

http://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html

Database level

CREATE DATABASE czech_slovak_names CHARACTER SET = 'keybcs2' COLLATE = 'keybcs2_bin';
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';

Table level

CREATE TABLE english_names (id INT, name VARCHAR(40))
CHARACTER SET 'utf8'
COLLATE 'utf8_icelandic_ci';

ALTER TABLE table_name
CONVERT TO CHARACTER
SET charset_name [COLLATE collation_name];

ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;

Column level

CREATE TABLE european_names (
 croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
 greek_names VARCHAR(40) CHARACTER SET 'greek');

To store emoji, or non utf8 character

 ALTER TABLE personals
 MODIFY self_introduction
 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

SQL in 10 minutes notes

Lesson 6 – Wildcard

SELECT prod_name 
FROM Products 
WHERE prod_name 
LIKE 'F%y';
% represents zero, one, or more characters at the specified location 
in the search pattern

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name 
LIKE '__ inch teddy bear';
The underscore is used just like %, but instead of matching multiple 
characters the underscore matches just a single character

SELECT cust_contact 
FROM Customers 
WHERE cust_contact 
LIKE '[JM]%' 
ORDER BY cust_contact;
The brackets ([]) wildcard is used to specify a set of characters, 
any one of which must match a character in the specified position

Lesson 7 – Creating Calculated Fields

SELECT CONCAT(vend_name, ' (', vend_country, ')') AS vender_infor 
FROM Vendors 
ORDER BY vend_name;

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) +')' AS vender_infor 
FROM Vendors
ORDER BY vend_name;

The RTRIM() function trims all space from the right of a value. By using RTRIM(), the individual columns are all trimmed properly.

An alias is just that, an alternatename for a field or value. Aliases are assigned with the AS keyword.

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

How to use preload in Ruby on Rails

To avoid problem N + 1 in RoR, we can use preload, preload will load all association records using another sql query.

Example: we have model User, Post and Comment with relation:

User has_many Posts

Post has_many Comments

Now you want to show all male users associated with their posts and comments.

If you can do like this

User.preload(posts: :comments).where(gender: :male)

It will generate 3 sql query.

1. For load all user with gender male

2. Load all posts with user_id in id of first query

3. Load all comments with post_id in id of second query

So it only take 3 query for all users instead of 3 query for each users. It will save a lot of time

That’s all.

 

Validate uploaded file in Ruby on Rails

File Validators gem adds file size and content type validations to ActiveModel. Any module that uses ActiveModel, for example ActiveRecord, can use these file validators.

Install:

gem install file_validators

or add to Gemfile

gem 'file_validators'

Using

class Profile
  include ActiveModel::Validations

  attr_accessor :avatar
  validates :avatar, file_size: { less_than_or_equal_to: 100.kilobytes },
                     file_content_type: { allow: ['image/jpeg', 'image/png'] } 
end

Get facebook picture using Koala gem

This article is about loading facebook images using Koala GEM

Install Koala GEM

gem install koala

or add to Gemfile

gem 'koala'

To load pictures of facebook user, you have to get user access token, you can access https://developers.facebook.com/tools/explorer/ then click GET TOKEN

To read more about how to get photos from Facebook document

https://developers.facebook.com/docs/graph-api/reference/user/photos

https://developers.facebook.com/docs/graph-api/reference/v2.7/album/photos

You can get photos by specific album, or you can get photos from all albums. I will demo get photo from all albums. Normally, when you get photos, fb will return all photos include your tag photos. So if you want to get only your uploaded photos, you can add params type = ‘uploaded’. Notice that: you only can get maximum 25 images per request.

class LoadFbImage
  def initialize(token)
    @fb_graph = Koala::Facebook::API.new(token)
  end

  def get_fb_photos(page = nil)
    if page.present?
      data = @fb_graph.get_page(page)
    else
      data = @fb_graph.get_connections('me', 'photos', type: 'uploaded',
                        fields: 'images', limit: 25)
    end

    {
      pictures: data.map{ |p| p['images'].first["source"] },
      # a list of pictures url
      # fb will return images with many size, so I only get first result
      next_page_params: data.next_page_params
      # next_page_params needs when want toload more image
      # next_page_params will be nil in last page
    }
   end
end

if you use Graph API, you can write FQL Query like

GET /{user-id}/photos?type=uploaded

if you want to load images that belongs to specific album, you can write

GET /{album-id}/photos?type=uploaded

That’s all.