Tuesday, November 3, 2015

Java Jdbc Test for UTF8 and Default Value for TIMESTAMP

Just a quick MySQL test on how to use UTF8 encoding with JDBC connection string. Also a test on how to set TIMESTAMP default values.

package zemian.jdbc;

import org.junit.Test;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**

 -- drop table ztest_issues;
 CREATE TABLE ztest_issues(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 , title VARCHAR(64) NOT NULL
 , summary TEXT NULL
 , priority INT NOT NULL DEFAULT 5
 , cdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 );

 INSERT INTO ztest_issues(title) VALUES('test');
 INSERT INTO ztest_issues(title, summary) VALUES('test2', 'Just a test');
 INSERT INTO ztest_issues(title, summary, priority) VALUES('test2', 'Just a test', 1);
 INSERT INTO ztest_issues(title, summary, priority, cdate) VALUES('test2', 'Just a test', 1, '2010-12-31 08:00:00');

 --INSERT INTO ztest_issues(title, summary) VALUES('locale test1', LOAD_FILE('C:/data/tmp/test.xml'));
 --INSERT INTO ztest_issues(title, summary) VALUES('locale test2', LOAD_FILE('C:/data/tmp/test2.xml'));
 --INSERT INTO ztest_issues(title, summary) VALUES('locale test3', LOAD_FILE('C:/data/tmp/test3.xml'));

 SELECT * FROM ztest_issues;

 NOTE:
 CURRENT_TIMESTAMP is version specific and is now allowed for DATETIME columns as of version 5.6 http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

 For 5.6 <, use TIMESTAMP for cdate field instead.

 cdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 cdate DATETIME NULL

 NOTE2:
 cdate DATETIME NOT NULL DEFAULT 0

 If you were to use default to ZERO, then you would need this properties in conn string. Else you will fail to get ZERO date value into Java.

 You need to tell the JDBC driver to convert them to NULL. This is done by passing a connection property name zeroDateTimeBehavior with the value convertToNull

 For more details see the manual: http://dev.mysql.com/doc/refman/4.1/en/connector-j-installing-upgrading.html

 */
public class ZtestIssuesJdbcTest {
//    String url = "jdbc:mysql://localhost/test";
//    String url = "jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull";
    String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8_general_ci&zeroDateTimeBehavior=convertToNull";
    String username = "test";
    String password = "test123";

    @Test
    public void testShowTableLocale() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "SHOW VARIABLES LIKE 'char%'";
            System.out.println("Sql:" + sql);
            Statement sm = conn.createStatement();
            ResultSet rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%s\t%s\n", rs.getObject(1), rs.getObject(2));
            }

            sql = "SHOW CREATE TABLE ztest_issues";
            System.out.println("Sql:" + sql);
            sm = conn.createStatement();
            rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%s\t%s\n", rs.getObject(1), rs.getObject(2));
            }
        }
    }

    @Test
    public void testQuery() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "SELECT id, cdate, title, summary FROM ztest_issues";
            System.out.println("Sql:" + sql);
            Statement sm = conn.createStatement();
            ResultSet rs = sm.executeQuery(sql);
            while (rs.next()) {
                System.out.printf("%d\t%s\t%s\t%s\n", rs.getObject(1), rs.getObject(2), rs.getObject(3), rs.getObject(4));
            }
        }
    }

    @Test
    public void testInsert() throws Exception {
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "INSERT INTO ztest_issues(title, summary, cdate) VALUES(?, ?, ?)";
            System.out.println("Sql: " + sql);
            PreparedStatement ps = conn.prepareStatement(sql);

            String testId = "" + System.currentTimeMillis();

            ps.setObject(1, "English Locale " + testId);
            ps.setObject(2, "Just a test");
            ps.setObject(3, new Date());
            int result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);

            ps.setObject(1, "Chinese Locale " + testId);
            ps.setObject(2, "只是一個測試");
            ps.setObject(3, new Date());
            result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);

            ps.setObject(1, "Spanish Locale " + testId);
            ps.setObject(2, "Sólo una prueba");
            ps.setObject(3, new Date());
            result = ps.executeUpdate();
            System.out.println("Insert Result: " + result);
        }
    }
}

Monday, November 2, 2015

ConEmu - a Terminal Windows or Tab Manager

Just learned about ConEmu project at https://conemu.github.io and it's pretty awesome!

Thanks Onur for the tips!

Sunday, October 25, 2015

mac: How to view Unix man pages with browser

I have come across this wonderful project called Bwana (https://www.bruji.com/bwana/) for Mac. Install it and you can view any man page on the browser. For example try typing the following address on the Safari:

man:find

And you will see something like this:


Thursday, October 22, 2015

python: How to setup a new Trac issue tracking system

Here is how I setup a local instance of Trac (a python based issue tracking web application) on my Mac.

Prerequisite: MySQL 5.6 and Python 2.7 (Python3 will not work with Trac yet!)

Step1: Setup a Trac database and a user

sql> CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
sql> CREATE USER 'dev'@'localist' IDENTIFIED BY 'dev123';
sql> GRANT ALL ON trac.* TO 'dev'@'localhost';

Step2: Install MySQL adaptor for Python and Trac

bash> pip install Genshi trac mysqlclient
Step3: Setup a Track instance

bash> trac-admin /Users/zemian/dev/mytrac intent
bash> # Above will prompt you to enter a backend string. Use
bash> # this connection string: 
bash> #   mysql://dev:dev123@localhost:3306/trac

Step3: Create a Trac admin user
bash> htpasswd -c /Users/zemian/dev/mytrac/.htpasswd admin

Step4: Run Track
bash> tracd -p 8000 --basic-auth="mytrac,/Users/zemian/dev/mytrac/.htpasswd,mytrac" /Users/zemian/dev/metric

Saturday, October 17, 2015

python: mysqlclient gives "Library not loaded: libmysqlclient.18.dylib" error

If you want to use python MySQLdb module (eg: if you run Trac with MySQL backend), you would need first install MySQL server on MacOSX, then install the mysqlclient python package using pip. However upon verifying it, you may encounter error like this:

(mypy-test)Zemians-Air:dev zemian$ pip install mysqlclient
Collecting mysqlclient
  Using cached mysqlclient-1.3.6.tar.gz
Building wheels for collected packages: mysqlclient
  Running setup.py bdist_wheel for mysqlclient
  Stored in directory: /Users/zemian/Library/Caches/pip/wheels/9c/3b/73/8f16f45dc76999dafc2af06b0d6e1e669bc0e1594f41fcc2e8
Successfully built mysqlclient
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.3.6
(mypy-test)Zemians-Air:dev zemian$ python
Python 2.7.10 (default, Aug 22 2015, 20:33:39) 
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.0.59.1)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/zemian/dev/mypy-test/lib/python2.7/site-packages/MySQLdb/__init__.py", line 19, in <module>
    import _mysql
ImportError: dlopen(/Users/zemian/dev/mypy-test/lib/python2.7/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
  Referenced from: /Users/zemian/dev/mypy-test/lib/python2.7/site-packages/_mysql.so
  Reason: image not found

To resolve this, you need to add the following to your shell environment

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib

Monday, October 12, 2015

mysql: How to specify SAME target table for update in FROM clause

Have you tried updating something simple as following?

update category_tmp set last_update=NOW() 
where category_id in (
  select category_id from category_tmp where name like 'A%'
);

In MySQL you will get an error like this:

Error Code: 1093. You can't specify target table 'category_tmp' for update in FROM clause.

So it says that you can't use the same update TABLE name within the sub query in where condition. The trick to get around this is to use another sub query in the where clause so it won't see the TABLE name being used! Here is a workaround:

update category_tmp set last_update=NOW() 
where category_id in (
  select category_id from (
    select category_id from category_tmp where name like 'A%'
  ) ID_LIST
);

Noticed that in MySQL, you must name your sub query result such as ID_LIST, in order for it to be re-select it again on the outer query! Otherwise it will error out with this:

Error Code: 1248. Every derived table must have its own alias


Sunday, October 11, 2015

mac: MacBook Air is not displaying scroll bar on Finder

For odd reason that I don't see scrollbar shows up on Finder in MacBook Air when I have a long list of files. Can't scroll down with my trackpad, but I can do it with "Fn" + Down Arrow key though. To fix this, I have to go into System Preference, General and set "Show scroll bar" to "Always".