Nasrul Hazim Bin Mohamad

MySQL: Bulk Remove Redundant Index

Oct
31
  1. Find the [TARGET_DATABSE] and replace it with the target database
  2. Execute the query
  3. Copy the result and execute each of the SQL statement to remove the index
SELECT
CONCAT(GROUP_CONCAT('ALTER TABLE `',tab_left.TABLE_NAME,'` DROP INDEX ',
(
CASE 
WHEN tab_left.COLUMNS = tab_right.COLUMNS AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN GREATEST(tab_left.INDEX_NAME, tab_right.INDEX_NAME) 
ELSE tab_left.INDEX_NAME 
END 
) SEPARATOR ';n')) AS q 
FROM 
(
SELECT 
TABLE_NAME, 
CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, 
INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(
CONCAT(
'`', COLUMN_NAME, '`') ORDER BY 
IF( INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), 
COLUMN_NAME) COLUMNS FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_left 
INNER JOIN (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY IF(INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME 
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_right 
ON tab_left.TABLE_NAME = tab_right.TABLE_NAME AND tab_left.INDEX_NAME != tab_right.INDEX_NAME 
AND tab_left.INDEX_TYPE = tab_right.INDEX_TYPE AND CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS 
AND (tab_left.IS_UNIQUE = 'NO' OR tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN TRUE WHEN tab_left.INDEX_TYPE = 'BTREE' AND INSTR(tab_right.COLUMNS, tab_left.COLUMNS) = 1 AND tab_left.IS_UNIQUE = 'NO' 
THEN TRUE ELSE FALSE END GROUP BY tab_left.TABLE_NAME; 

MySQL: Bulk PK Rename

Oct
29
SELECT CONCAT(
		GROUP_CONCAT('ALTER TABLE `',a.TABLE_NAME,'`',
		' CHANGE `',a.TABLE_NAME,'`.`',a.COLUMN_NAME,'` ',
		'`',a.TABLE_NAME,'`.`id` ',a.COLUMN_TYPE
	SEPARATOR ';n')
) AS q
FROM `information_schema`.`COLUMNS` a
WHERE a.`TABLE_SCHEMA` = 'target_database' AND a.`COLUMN_KEY` = 'PRI';

Copy & Paste the result in your MySQL editor and execute the SQL.

References
  1. http://stackoverflow.com/questions/7691816/group-concat-comma-separator-mysql
  2. http://stackoverflow.com/questions/893874/mysql-determine-tables-primary-key-dynamically/893922#893922
  3. http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html

How to set prefix to multiple tables at one time

Oct
23

Set Max Length for Group Concatenation

SET group_concat_max_len = 6144;

Generate the SQL

SELECT 
    CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`rgbis_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='target_database';

Once above SQL generated, copy & paste in your SQL editor, and execute it.

MySQL – Camel Case Function

Jun
11

Camel Case Function

DELIMITER $$

DROP FUNCTION IF EXISTS `pCase` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `pCase`(str TEXT) RETURNS TEXT CHARSET latin1
    DETERMINISTIC
BEGIN
  DECLARE result TEXT DEFAULT '';
  DECLARE word TEXT DEFAULT '';
  DECLARE working_char TEXT DEFAULT '';
  DECLARE last_space INT DEFAULT 0;
  DECLARE word_boundry TEXT DEFAULT ' (){}[]-/'; #Used to decide when a new word begins.  Add you boundries here!!
  DECLARE ucase_words TEXT DEFAULT 'UK,USA,DVD,DVDs,GB'; #To make some words uppercase use ucase_words below.  The text entered here is used so dvds becomes DVDs.
  DECLARE i INT DEFAULT 1;  #Loop counter
  DECLARE found_boundry INT DEFAULT 1; #When we find a boundry set to 1 (True) else 0 (False)

  # handle NULL
  IF (str IS NULL) THEN
    RETURN NULL;
  END IF;

  # if 0 length string given
  IF (CHAR_LENGTH(str) = 0) THEN
    RETURN '';
  END IF;

  SET str = LOWER(str);

  # loop through each letter looking for a word boundry
  WHILE(i <= (LENGTH(str)+1)) DO

    #Set our working charater
    SET working_char=SUBSTRING(str, i-1, 1);

    #Find a word boundry
    IF(LOCATE(working_char, word_boundry)>0) THEN
      #Check if last word was in our uppercase list, using the example in the list to allow dvds to become DVDs
      IF(LOCATE(word, ucase_words)>0) THEN
        SET result=CONCAT(LEFT(result,(LENGTH(result)-LENGTH(word))),MID(ucase_words,LOCATE(word, ucase_words),LENGTH(word)));
      END IF;

      SET found_boundry=1;  #Set the boundry flag, then ignore
      SET result=CONCAT(result, working_char);
      SET word=''; #Reset word
    ELSE
      SET word=CONCAT(word, working_char);
      IF(found_boundry=1) THEN
        SET result = CONCAT(result, UPPER(working_char));  #After a boundry so upper case
        SET found_boundry=0;
      ELSE
        SET result = CONCAT(result, working_char);
      END IF;

    END IF;

    SET i=i+1;

  END WHILE;

  #Check if last word was in our uppercase list
  IF(LOCATE(word, ucase_words)>0) THEN
    SET result=CONCAT(LEFT(result,(LENGTH(result)-LENGTH(word))),MID(ucase_words,LOCATE(word, ucase_words),LENGTH(word)));
  END IF;

        RETURN result;

END $$

DELIMITER ;

Usage

SELECT pCase(`desired_column`) FROM `t`;

Reference: MySQL Bugs: #2430

Android – Connect to PHP

Feb
13

Search keyword: android connect to php

Here, I’m going to show the simplest way to connect to the PHP in Android – receiving JSON(JavaScript Object Notation) upon successful. You could make a simple JSON response in a PHP script like the following and deploy it to the server / localhost.

$arr = (object) array();
$arr->str = "String";
$arr->number = 1;
$arr->rightOrWrong = true;
$arr->nothing = null;
$arr->decimal = 10.209;
$arr->arr = array(1,2,3,4);
$arr->obj = (object) array('one' => 1, 'two' => 2, 'three' => 3, 'four' => 4);
$arr->title = "This is a JSON object";

echo json_encode($arr);

I’m creating a new Android project in Eclipse and extending the AsyncTask class in order to connect to PHP, and using HtppGet to make a connection to the server(can be Windows / Linux base).

package com.rndxpress.androidphpmysql;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.os.AsyncTask;
import android.util.Log;

public class Conn extends AsyncTask<Void, Void, Void> {
	
	private static String convertStreamToString(InputStream is) {
        
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder();
 
        String line = null;
        try {
            while ((line = reader.readLine()) != null) {
                sb.append(line + "n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return sb.toString();
    }
	@Override
	protected Void doInBackground(Void... params) {
	String url = "http://[domain-name]/[json-interface]";
		
        HttpClient httpclient = new DefaultHttpClient();
 
        HttpGet httpget = new HttpGet(url); 
 
        HttpResponse response;
        try {
        	
            response = httpclient.execute(httpget);
            // Examine the response status
            Log.i("Conn::response",response.getStatusLine().toString());
 
            HttpEntity entity = response.getEntity();
            
            if (entity != null) {
            	 
                // A Simple JSON Response Read
                InputStream instream = entity.getContent();
                String result = convertStreamToString(instream);
                Log.i("Conn::result",result);
 
                // A Simple JSONObject Creation
                JSONObject json=new JSONObject(result);
                Log.i("Conn::JSONObject",json.toString());
                
                instream.close();
            }
        } catch (ClientProtocolException e) {
        	Log.i("Conn::ClientProtocolException",e.getMessage());
        } catch (IOException e) {
        	Log.i("Conn::IOException",e.getMessage());
        } catch (JSONException e) {
        	Log.i("Conn::JSONException",e.getMessage());
        } catch (Error e) {
        	Log.i("Conn::Error",e.getMessage());
        } 
		return null;
	}
}

Following are the screenshot from my Galaxy Nexus & result logged in Eclipse.

Once you able to received result / response, next step you can further develop apps for more complex operations with PHP, MySQL or any further processing on the server side.

Simple UI - a button with click event to connect to the server.

Simple UI – a button with click event to connect to the server.

Logged result - JSON.toString()

Logged result – JSON.toString();

MySql and Deathly Swallows

Jan
28

mysql super user creation steps.

Jan
28