Thursday, October 24, 2013

Change the thought when cracking a special type of algorithm problem


Problem: (Gas station)
There are N gas stations along a circular route, where the amount of gas at station i is gas[i].
You have a car with an unlimited gas tank and it costs cost[i] of gas to travel from station i to its next station (i+1). You begin the journey with an empty tank at one of the gas stations.
Return the starting gas station's index if you can travel around the circuit once, otherwise return -1.
Solutions:
The first thought of this problem is to check whether there exists an index that can meet the requirement and check each of them in serial until we find one. Well, you will get a O(n^2) algorithm if you do so.
My second thought is, this problem can use dynamic programming to record the path in order to minimize the computation. However, it is still a O(n^2) algorithm. 
After reading the solution of a guy from Leetcode.com, I realize that I went to the wrong way at the beginning. If we take a look at the problem, it is actually asking to "return ONE index that satisfy the requirement". Wow, I never notice that. So let's ask "What the most possible index?". If this index does not pass the constraints, there would no index left. As such, we only need to check the MOST POSSIBLE index and get a O(n) algorithm. 
class Solution {
public:
    int canCompleteCircuit(vector<int> &gas, vector<int> &cost) {
        if (gas.size() == 0)
            return -1;

        int sum = 0;
        int index = -1;
        bool last = true;
        for (int i = 0; i < gas.size(); i++) {
            int diff = gas[i] - cost[i];
            if (last && diff >= 0 && sum <= 0)
                index = i;
            last = (diff < 0);
            sum += diff;
        }

        if (index == -1)
            return index;

        for (int i = 0, sum = 0; i < gas.size(); i++) {
            sum += gas[(index + i) % gas.size()] - cost[(index + i) % gas.size()];
            if (sum < 0)
                return -1;
        }

        return index;
    }
};



Tuesday, July 16, 2013

Immigrate data from SQLite3 to MySQL on Linux

This is a classical problem of how to immigrate data from SQLite3 to MySQL. I tried lots of scripts found online and the following one is the best suitable to me.

The code is from https://github.com/athlite/sqlite3-to-mysql/blob/master/sqlite3-to-mysql  and the author should take the whole credit.
#/usr/bin/env sh
sed \
-e '/PRAGMA.*;/ d' \
-e '/BEGIN TRANSACTION.*/ d' \
-e '/COMMIT;/ d' \
-e '/.*sqlite_sequence.*;/d' \
-e 's/"/`/g' \
-e 's/CREATE TABLE \(`\w\+`\)/DROP TABLE IF EXISTS \1;\nCREATE TABLE \1/' \
-e 's/\(CREATE TABLE.*\)\(PRIMARY KEY\) \(AUTOINCREMENT\)\(.*\)\();\)/\1AUTO_INCREMENT\4, PRIMARY KEY(id)\5/' \
-e "s/'t'/1/g" \
-e "s/'f'/0/g" \
$1
 Here are the full process of immigrating SQLite3 to MySQL in Linux (Ubuntu for me).
1) Make sure that you have installed sqlite3 which is a management software for sqlite database. Otherwise,
    $  sudo apt-get install sqlite3
2) Save the above code in a file like sqlite32mysql.sh and assign execute privilege.
    $  chmod +x sqlite32mysql.sh
3) If you don't have a database, then create one
     mysql -u xx -p xx -h 127.0.0.1 "CREATE DATABASE sample IF NOT EXIST"
4) If you'd like only import the data to old tables,
      $  sqlite3 your_sqlite3_db.db .dump|grep "^INSERT"| ./sqlite32mysql.sh >dump.sql
   Otherwise create new tables,
     $   sqlite3 your_sqlite3_db.db .dump| ./sqlite32mysql.sh >dump.sql
5) If there are still some records cannot be imported correctly, do it separately using the following command   to grab them out.
     $ sed -n "starter_num, end_line_num p" dump.sql > manual.sql
6) Load the dump.sql using mysql.
    $ mysql -u xx -p xx -h 127.0.0.1 sample <dump.sql

BINGO!

Remove duplicated rows

Problem: 
Assume you have a table with structure as follows.
    Comment [id, app_id, reviewer_id,review_id ....] where id is primary key.
And you have to remove duplicated records with the same (app_id, and review_id).
Here are some solutions to fulfill your requirement.

Solution 1: [For small size of table]

 1) Find out which records are duplicated.
    SELECT id FROM (SELECT COUNT(*) AS num, id, app_id, review_id FROM Comment GROUP BY app_id,review_id) AS result WHERE result.num>2;
 NOTE: the above sql statement will list one of duplicated id;

2) Discard duplicated records
   DELETE FROM Comment WHERE id in (SELECT id FROM (SELECT COUNT(*) AS num, id, app_id, review_id FROM Comment GROUP BY app_id,review_id) AS result WHERE result.num>2)


Solution 2: [Once for all]

    ALTER IGNORE TABLE Comment ADD UNIQUE INDEX idx_name (app_id,review_id);

This solution works for me so that I did not examine its limitation in some extreme cases. 

The credit of this solution goes to http://stackoverflow.com/a/3312066 . 

Monday, July 15, 2013

EPFImporter with error [WARNING]: Incorrect string value: '\xE6\xB0\x91\xE8\xA8\xB4...' for column 'title' at row 15

When using EPFImporter provided by Apple .Inc to import EPF data into table, it reported the following errors.

[WARNING]: Incorrect string value: '\xE6\xB0\x91\xE8\xA8\xB4...' for column 'title' at row 15

The reason is that the encoding of the column title is utf8 (assuming it is utf8) which cannot store characters larger than 3 bytes. For mysql, utf8 is designed as 3 bytes. After MySQL 5.5, they add another type utf8mb4 to deal with such problem  which is 4-byte encoding.

The solution is to make sure your table is look like this:
CREATE TABLE `epf_application_detail_tmp` (
`export_date` BIGINT(20) NULL DEFAULT NULL,
`application_id` INT(11) NOT NULL DEFAULT '0',
`language_code` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
`title` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`description` LONGTEXT NULL COLLATE 'utf8mb4_unicode_ci',
`release_notes` LONGTEXT NULL COLLATE 'utf8mb4_unicode_ci',
`company_url` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`support_url` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_1` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_2` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_3` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_url_4` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_3` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`screenshot_width_height_4` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_1` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_2` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_3` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_url_4` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_3` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
`ipad_screenshot_width_height_4` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
PRIMARY KEY (`application_id`, `language_code`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

For convenience, modify the default setting of mysql.

add the following to my.cnf
[mysqld]
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4

Then, restart mysql and then create database e.g., epf to store EPF data. If you create epf already, make sure to change its default setting.

Friday, January 25, 2013

How to: Move the MySQL data directory in *buntu


  1. Open the terminal
  2. Stop MySQL with the command "sudo /etc/init.d/mysql stop".
  3. Copy the existing data directory (by default located in /var/lib/mysql) using the command "sudo cp -R -p /var/lib/mysql /path/to/new/datadir". All you need are the data files, so delete the others with the command "sudo rm /path/to/new/datadir". (You will get a message about not being able to delete some directories, but that's what you want.)
  4. Edit the MySQL configuration file with the command "gksu gedit /etc/mysql/my.cnf". Find the entry for "datadir", and change the path (which should be "/var/lib/mysql") to the new data directory.
  5. NOW FOR THE PART THE OTHER TUTORIALS DON'T MENTION... From 7.10 (Gutsy Gibbon) forward, Ubuntu uses some security software called AppArmor that specifies the areas of your filesystem applications are allowed to access. Unless you modify the AppArmor profile for MySQL, you'll never be able to restart MySQL with the new datadir location.
    • In the terminal, enter the command "sudo gedit /etc/apparmor.d/usr.sbin.mysqld".
    • Copy the lines beginning with "/var/lib/mysql", comment out the originals with hash marks ("#"), and paste the lines below the originals.
    • Now change "/var/lib/mysql" in the two new lines with "/path/to/new/datadir". Save and close the file.
    • Reload the AppArmor profiles with the command "sudo /etc/init.d/apparmor reload".
  6. Restart MySQL with the command "sudo /etc/init.d/mysql restart". With any luck, MySQL will start with no errors, and your data will be stored in the new location!
Credit goes to http://ubuntuforums.org/showthread.php?t=897354

Problems met when installing ubuntu with RAID1

0. "BOOTMGR is missing" when installing from usb
A: Format the drive as FAT (not FAT32)


1. Unable to install GRUB in /dev/sdb
A: From the link http://askubuntu.com/questions/43036/how-do-i-install-grub-on-a-raid-system-installation

Many of the answers here are just plain incorrect, telling you to disable BIOS RAID! The correct solution is atthis blog entry. I'll summarize it below.
At the stage of the install where it is attempting to install GRUB it will detect as
/dev/mapper
This is incomplete! That's why the GRUB install fails.
You need the actual name of the RAID array to install to. So during that step, pressctrl+alt+F2 to drop to a busybox terminal, then enter
ls -l /dev/mapper
Pick out the name of your array from the list shown, then press ctrl+alt+F1 to switch back to the install (you can switch back and forth as much as you like with no problems) and enter it in the field as
/dev/mapper/{your array name}
then GRUB installs perfectly and you're ready to go, with a proper BIOS RAID array intact.
It works in my case...

2. What is the default root password of ubuntu?
A: just sudo -i to enter the root mode. 

3. How to enter graphical mode from command line?
A: Plz refer to  http://www.linuxquestions.org/questions/ubuntu-63/%5Bstartx-problem%5D-startx-goes-to-black-grey-screen-and-comes-back-to-command-prompt-853802/
sudo apt-get install xinit
sudo apt-get install ubuntu-desktop  (it takes a long long time...)