Open or not open, that is the stupid question.

[Level 1] How to get ram spec in Ubuntu

2012-02-16T14:59:00.001+08:00

If you want to get the ram module information from Ubuntu.
Please use command dmidecode with type 17.
ex.

$ sudo dmidecode --type 17 | more

Wish this helps.
regards,
Stanley Huang

[Level 1] Install maven in Ubuntu

2012-01-19T14:25:00.002+08:00

In Ubuntu, you could use apt-get to install maven.

# apt-get -y install maven2

Wish this helps.
regards,
Stanley Huang

[Level 1] Install git in Ubuntu

2012-01-19T14:24:00.002+08:00

If you want to install git in Ubuntu, you could use apt-get command:

# apt-get -y install git-core git-doc git-gui gitk

Wish this helps.
regards,
Stanley Huang

[Level 3] Cassandra stress tool from git in Ubuntu.

2012-01-19T14:21:00.002+08:00

If you want to do the stress test for Cassandra.
You could try an open source project call cassandra-stress of zznate.
First you must to have git client and maven.

Please reference here: git installation, maven installation

Clone the src from git.

# git clone https://github.com/zznate/cassandra-stress.git

Then you would find a folder named "cassandra-stress", cd it and build the binary.

# cd ./cassandra-stress
# mvn install

and mvn would create a sub folder "target", cd it and run the stress script.

# cd ./target/appassembler/
# sh ./bin/stress -o insert -b 1000 -n 2000000 localhost:9160

Wish this helps.
regards,
Stanley Huang

[Level 3] Run python in shell script.

2012-01-18T14:58:00.000+08:00

If you want to wrap python script into shell script.
You could use the following sample code.

#!/bin/sh
# -*- mode: Python -*-

""":"
# bash code here; finds a suitable python interpreter and execs this file.
# prefer unqualified "python" if suitable:
python -c 'import sys; sys.exit(sys.hexversion < 0x020500b0)' 2>/dev/null \
    && exec python "$0" "$@"
for pyver in 2.6 2.7 2.5; do
    which python$pyver > /dev/null 2>&1 && exec python$pyver "$0" "$@"
done
echo "No appropriate python interpreter found." >&2
exit 1
":"""

import os, sys 

print os.path.dirname('/Hello/World/c.txt')

$ ./test.sh
/Hello/World

Wish this helps. regards, Stanley Huang

[Level 2] Cassandra token generation tool by Python.

2012-01-16T16:54:00.000+08:00

#!/bin/env python
import sys 
if (len(sys.argv) > 1): 
    num=int(sys.argv[1])
else:
    num=int(raw_input("How many nodes are in your cluster? "))
for i in range(0, num):
    print 'node %d: %d' % (i, (i*(2**127)/num))

$ ./cassandraTokenGenTools.py 
How many nodes are in your cluster? 5 
node 0: 0
node 1: 34028236692093846346337460743176821145
node 2: 68056473384187692692674921486353642291
node 3: 102084710076281539039012382229530463436
node 4: 136112946768375385385349842972707284582

Wish this helps.
regards,
Stanley Huang

[Level 2] JSON module in Python.

2012-01-16T16:46:00.001+08:00

#!/bin/env python
import json
import random

data = {}
for i in range(0,2):
    key = 'i%s' % i 
    data[key] = {}
    for j in range(0,3):
        subkey = 'j%s' % j 
        data[key][subkey] = {}
        for k in range(0,4):
            item = 'k%s' % k 
            value = int(random.random() * 100)
            data[key][subkey][item] = value
print json.dumps(data)

$ ./testPython.py 
{"i1": {"j0": {"k3": 93, "k2": 98, "k1": 0, "k0": 3}, "j1": {"k3": 53, "k2": 28, "k1": 99, "k0": 69}, "j2": {"k3": 3, "k2": 93, "k1": 74, "k0": 28}}, "i0": {"j0": {"k3": 67, "k2": 98, "k1": 15, "k0": 92}, "j1": {"k3": 70, "k2": 22, "k1": 63, "k0": 3}, "j2": {"k3": 88, "k2": 28, "k1": 30, "k0": 54}}}

Wish this helps.
regards,
Stanley Huang

[Level 3] Add row count in select of MySQL.

2012-01-16T02:47:00.001+08:00

mysql> set @row_count;
mysql> select *, @row_count := @row_count + 1 as row_count from test;
+------+---------+-----------+
| id   | product | row_count |
+------+---------+-----------+
|    1 | cloth   |         1 |
|    2 | shoes   |         2 |
|    3 | paints  |         3 |
+------+---------+-----------+
10 rows in set (0.00 sec)

Wish this helps.
regards,
Stanley Huang

[Level 3] rw_ether_atob, rw_ether_btoa stored function in MySQL.

2012-01-16T01:04:00.000+08:00

delimiter //

drop function if exists rw_ether_atob//
create function rw_ether_atob(sAscii char(17))
returns bit(48)
deterministic
begin
  declare bReturn bit(48);
  ##set bReturn=conv(replace(sAscii,':',''),16,2); ## string output not bit
  ##set bReturn=conv(replace(sAscii,':',''),16,10); ## not work
  ##set bReturn=cast(replace(sAscii,':','') as bit(48)); ## syntax error
  ##set bReturn=bin(replace(sAscii,':','')); ## syntax error
  set bReturn=unhex(replace(sAscii,':',''));
  return bReturn;
end//

drop function if exists rw_ether_btoa//
create function rw_ether_btoa(sBit bit(48))
returns char(17)
deterministic
begin
  declare sReturn char(17);
  set sReturn=lpad(hex(sBit),12,'0');
  set sReturn=concat_ws(':', substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2), substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2));
  return sReturn;
end//

delimiter ;

/*
mysql> create table ether_table (b bit(48), a char(17), index(b), index(a));
Query OK, 0 rows affected (0.67 sec)

mysql> insert into ether_table values (rw_ether_atob('00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF'); 
Query OK, 1 row affected (0.01 sec)

mysql> select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+-------------------+-------------------+
| rw_ether_btoa(b)  | a                 |
+-------------------+-------------------+
| 00:CD:EF:00:CD:EF | 00:CD:EF:00:CD:EF |
+-------------------+-------------------+
1 rows in set (0.01 sec)

mysql> explain select rw_ether_btoa(b), a from ether_table
    -> where b=rw_ether_atob('00:CD:EF:00:CD:EF');
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | ether_table | ALL  | NULL          | NULL | NULL    | NULL | 1002 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

*/

Wish this helps. regards, Stanley Huang

[Level 1] Notes for MySQL. (config)

2012-01-16T01:00:00.001+08:00

## take care with signature '???'

[mysql]
user=root
password
database=mysql
#!include /etc/my.inc
#prompt=\R:\m \d>
#prompt=\u:\h \d>

[mysqldump]

[client]
## new cert
#ssl-key=/usr/local/mysql/newcerts/client-key.pem
#ssl-cert=/usr/local/mysql/newcerts/client-cert.pem
#ssl-ca=/usr/local/mysql/newcerts/ca-cert.pem
user=root
host=localhost
protocol=tcp
compress
password

######################################################[mysqld_multi]
[mysqld_multi]
server-id  = 0
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/mysql/data2/hostname.pid2
datadir    = /usr/local/mysql/data2
language   = /usr/local/mysql/share/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/safe_mysqld/safe_mysqld
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/data3/hostname.pid3
datadir    = /usr/local/mysql/data3
language   = /usr/local/mysql/share/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /usr/local/mysql/data4/hostname.pid4
datadir    = /usr/local/mysql/data4
language   = /usr/local/mysql/share/mysql/estonia
user       = unix_user3
 
[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /usr/local/mysql/data6/hostname.pid6
datadir    = /usr/local/mysql/data6
language   = /usr/local/mysql/share/mysql/japanese
user       = unix_user4
######################################################[mysqld_multi]

[mysqld]
server-id=0
basedir=/usr/local/mysql
datadir=/Karajon/MySQLData # default $basedir/data
skip-name-resolve

## new cert
#ssl-key=/usr/local/mysql/newcerts/server-key.pem
#ssl-cert=/usr/local/mysql/newcerts/server-cert.pem
#ssl-ca=/usr/local/mysql/newcerts/ca-cert.pem

## ignore storage engine
#skip-innodb
#skip-myisam # cannot skip myisam
#skip-memory  # skip memory is not working, but no error.
#skip-blackhole
#skip-archive
#skip-mrg_myisam
#skip-csv
##skip-federated # no working, even enable federate.
#default-storage-engine=InnoDB # work in 5.1.38

################################################### enable federate
federated 

################################################### setting log
#log                       # default: off, on: log=$datadir/$hostname.log
log-bin=/Karajon/bin-log/stanley-nb-bin                   # default: off, on: log-bin=$datadir/$hostname-bin
binlog_format=row         # default: statement. BINLOG_FORMAT=[row|statement|mixed|default]


#max_binlog_cache_size=xxx # default: max_binlog_cache_size=18446744073709547520
#max_binlog_size=xxx       # default: max_binlog_size=1073741824
expire_logs_days=7 # default: expire_log_days=0 # never purge log files

log-slow-queries  # default off, on: log-slow-queries=$datadir/$hostname-slow.log
long-query-time=5 # default: long-query-time=10, mysql> set long_query_time=10;
#log-error=/usr/local/mysql/data/Stanley-NB.err # default: $datadir/$hostname.err
#log-output=file  # default: file, file/table/none
#log-short-format # suppress the extra information
#log-queries-not-using-indexes

################################################### pid file
#pid-file=/usr/local/mysql/data/Stanley-NB.pid # default: $datadir/$hostname.pid
#socket=/tmp/mysql.sock

################################################### transaction isolation level
#transaction-isolation=READ-COMMIT

################################################### connections
thread_cache_size=0  # default: 0, no. of threads for reuse.
max_connections=200  # default max_connection=100


################################################### MyISAM settings
# modify MyISAM key cache, unit=K, M or G, 
# in mysql> set   global key_buffer_size=128*1024*1024
#           set @@global.key_buffer_size=128*1024*1024
key_buffer_size=128M            # default is 8M
bulk_insert_buffer_size=4194304 # default is 8M

################################################### InnoDB settings
## set values
#innodb_data_home_dir=/usr/local/mysql/data
#innodb_data_file_path=innodata1:10M
#innodb_data_file_path=innodata1:100M
#innodb_data_file_path=innodata1:100M:autoextend
#innodb_data_file_path=innodata1:100M:autoextend:max:500M
#innodb_data_file_path=innodata1:100M;innodata2:100M:autoextend
#innodb_log_file_size=50M
#innodb_log_files_in_group=2
innodb_buffer_pool_size=32M # default 8M
innodb_log_buffer_size=8M   # default 1M

## setting flag
innodb_file_per_table 
#loose_innodb_file_per_table ## if you use install plugin statements to install the InnoDB plugin, prepend loose_ for each InnoDB option.

################################################### skip engine
#skip-InnoDB
#skip-Memory

#
#transaction-isolation = READ-COMMITTED
# set global transaction isolation level [isolation_level];
# set session transaction isolation level [isoloation_level];

default-storage-engine=MyISAM
## select @@storage_engine;
## set @@storage_engine=InnoDB; ## in mysql prompt


################################################### for MyISAM storage only
myisam_data_pointer_size=7 ## default: 6

################################################### for memory storage only
#max_heap_table_size=16777216 ## default: max_heap_table_size=16777216, limit memory storage size


################################################### for unix only
## window is case insensitive
#lower_case_table_names=1

################################################### buffer & cache
sort_buffer_size=2048000
query_cache_size=8M ## default:0, sometimes fail why???
#query-cache-type=on ## fail why???


####################################################### for window only
#shared-memory
#enable-named-pipe
## set pipe name if enable named pipe.
#socket='c:/s.socket'

####################################################### for myisam auto-repair
myisam-recover=FORCE,BACKUP # options: DEFAULT,BACKUP,FORCE,QUICK


####################################################### sql mode
#sql_mode='no_auto_create_user' ## for user/security
sql_mode='traditional'

####################################################### network
max_allowed_packet=128M ## default:1048576, will begin with net_buffer_length and increasing if necessarily.
net_buffer_length=16384     ## default: 16384
#bind-address=127.0.0.1      ## bind ip with loopback interface.
#bind-address=172.31.255.100 ## bind ip with specific nic(172.31.255.100), to secure mysql on TCP/IP

####################################################### table
table_open_cache=64 # default: 64, no. of entries

####################################################### special
#skip_symbolic_links

####################################################### replication
## @master server, set bin log database
#binlog-do-db=db1
#binlog-do-db=db2
#binlog-do-db=db3
## @slaver server1, set replicated database
#replicate-do-db=db1
#replicate-do-db=db2
## @slaver server2, set replicated database
#replicate-do-db=db2
#replicate-do-db=db3

Wish this helps. regards, Stanley Huang

[Level 1] Testing notes for MySQL. (mysql commands)

2012-01-16T00:54:00.001+08:00

#!/usr/bin/bash
#ln -s /dev/null .mysql_history ## in my.cnf MYSQL_HISTFILE=/dev/null
#export MYSQL_PS1="(\u@\h) [\d]> " ## mysql --prompt="(\u@\h) [\d]> "
#mysql -u mysql mysql < ./test_mysql.sql
#mysql -u root mysql < ./test_mysql.sql
#mysql -u root --defaults-file=/etc/my.ini


## mysqlslap, benchmark test
# mysqlslap -auto-generate-sql # use default script
mysqlslap -a -c100 i10 -u root -p --engine=InnoDB,MyISAM,MEMORY,ARCHIVE

## mysqlcheck, for MyISAM, InnoDB, CSV and Archive.
#mysqlcheck world;
#mysqlcheck world City Country;
#mysqlcheck --databases world test;
#mysqlcheck --all-databases;

## bin-log
#mysqlbinlog bin.000001 bin.000002 | more
#mysqlbinlog bin.000001 bin.000002 | mysql
#mysqlbinlog --start-datetime="2009-01-01 00:00:00" --stop-datetime="2009-01-01 23:59:59" bin.000001 bin.000002 | mysql
#mysqlbinlog --start-position=1001 --stop-position=1999 bin.000001 bin.000002 | mysql
## purge binary logs
#mysql> purge binary logs to 'mysql-bin.010'; # mysql-bin.010 will not be deleted.
#mysql> purge binary logs before '2009-05-31 23:59:59';


## mysqlhotcopy, for MyISAM not for Innodb
## perl script, need to install DBI module first
#mysqlhotcopy world /var/archive
#mysqlhotcoyp world./Country/ /var/archive

mysql_multi
mysql_multi --example # list example configuration

## replication
mysqldump --master-data=2 
## option write to the dump file a comment
## line containing the replication coordinates, 
## which can be used when setting up replication slaves.
## That line has this format:
## CHANGE MASTER TO MASTER_LOG_FILE='file_name', MASTER_LOG_POS=file_position;

Wish this helps.
regards,
Stanley Huang

[Level 1] Testing notes for MySQL. (sql commands)

2012-01-16T00:51:00.002+08:00

The following commands are my testing notes for MySQL.

set sql_mode:='';

-- set sql_mode='only_full_group_by'; -- with group by syntax, select columns must be included in group by list.
set sql_mode='traditional';        -- a set of modes, fail sql transaction while datatype error or out of range.
-- set sql_mode='pipes_as_concat';    -- set || from logic 'or' to string concatenation (standard sql).
-- set sql_mode='ansi_quote';  -- ' for string, " for column name
select @@sql_mode;
-- set sql_mode='no_auto_create_user'; -- for not auto create user when grant, except grant with identified by.

######################################### notes
## select @@query_cache_type
##        @@query_cache_size
## show variable like 'query%';
## show status like 'qcache%';
## select sql_no_cache ...

/* mysql client */
/*
 \c to cancle mysql statement.
*/

/* command */
/*
show processlist;
show full processlist;
show databases;
show tables;
use mysql;
show table status like 'user'
show table status like '%user%';
show full tables;
show tables from information_schema;
show columns from information_schema.character_sets;
show character set;
show global status;
show create function/procedure func_name/proc_name;
show function/procedure status;
show function/procedure status like 'xxx';
show function/procedure status where db='xxx' and name='yyy';
show triggers;
show collation; ## [like 'utf8%']

show errors;
select @@error_count;
show count(*) errors;
show warnings;
select @@warning_count;
show count(*) warnings;

show variables;
pager;               -- pager on, default command is /usr/bin/less -ins.
pager /usr/bin/less -NJ10 ; -- show line no and jump 10 lines for once.
pager /usr/bin/more; -- set paging by command /usr/bin/more
nopager;             -- pager off.
show variables like 'charset%';

check table mysql.user;
repair table mysql.user; 
-- repair table support with engine
--   MyISAM
-- no support with engine
--   InnoDB, MEMORY
*/

/* create if not exists */
/*
create database if not exists test;
create table if not exists test (no int);
*/


/* special datatype */
/*
## enum max items: 65535
-- drop database christy; create database christy;
-- drop database if exists christy;
create database if not exists christy;
use christy;
create table mon_enum (
  Mon enum('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
);
insert into mon_enum values (1),(2),(3),('ABC'),('May'),('JUN');
select * from mon_enum;
create table mon_set (
  Mon set('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
);
insert into mon_set values('Jan');
insert into mon_set values(1),(2),(3),('ABC'),('May'),('JUN');
insert into mon_set values('Jan,Feb');
insert into mon_set values('Jan,Feb,Mar');
insert into mon_set values('Jan,XYZ,Mar');
insert into mon_set values('ABC');
insert into mon_set values('Jan, Feb,Mar, Apr,May'); -- result => 'Jan,Mar,May'
select * from mon_set;

## how many records can insert into testSetPK? if sql_mode is not strict_all_tables, strict_trans_tables
## => 4: '','a','b','a,b'
create table testSetPK (
  a set('a','b'),
  primary key(a)
) engine=MyISAM;
insert into testSetPK values ('a'),('b');
insert into testSetPK values ('b,a');
insert into testSetPK values ('a,b'); # ERROR 1062 (23000): Duplicate entry 'a,b' for key 'PRIMARY'
insert into testSetPK values ('ab'); # will insert empty '' into table.
select * from testSetPK;
# +-----+
# | a   |
# +-----+
# |     | 
# | a   | 
# | b   | 
# | a,b | 
# +-----+
# 4 rows in set (0.00 sec)


## how many records can insert into testEnumPK? if sql_mode is not strict_all_tables, strict_trans_tables
## => 3: '','a','b'
create table testEnumPK (
  a enum('a','b'),
  primary key(a)
) engine=MyISAM;
insert into testEnumPK values ('a'),('b'); # enum=1,2
insert into testEnumPK values ('abc'); # will insert empty '' into table. enum=0
select * from testEnumPK;
# +---+
# | a |
# +---+
# |   | 
# | a | 
# | b | 
# +---+
# 3 rows in set (0.00 sec)


*/

/* normal datatype */
/*
-- drop database chantelle; create database chantelle;
-- drop database if exists chantelle;
create database if not exists chantelle;
use chantelle;
set sql_mode='traditional';

-- auto_increment;
create table abc (i int);
insert into abc values (1),(2),(3),(4);

create table xyz (i int auto_increment, primary key(i));
insert into xyz values();
select last_insert_id(); -- => 1
insert into xyz values();
select last_insert_id(); -- => 2
insert into xyz values(),(),(),();
select last_insert_id(); -- => 3, not 6; get the first values generates for auto_increment.
delete from xyz where i between 2 and 5;
alter table xyz auto_increment=1;
select auto_increment from information_schema.tables
where table_schema='chantelle' and table_name='xyz'; -- => 6
delete from xyz where i >1;
alter table xyz auto_increment=1;
select auto_increment from information_schema.tables
where table_schema='chantelle' and table_name='xyz'; -- => 2



-- for testing zero fill
create table testZeroFill (
  n int(5) zerofill
);
insert into testZeroFill values (1),(11),(111),(1111),(11111);
insert into testZeroFill values (-1),(-11),(-111),(-1111),(-11111);


create table integers (
  n smallint unsigned
);
insert into integers values (-5);
select * from integers;
create table bits (
  b bit(10)
);
insert into bits values (b'1'),(b'101'),(b'1000001');
select * from bits;
*/

/* temporal data type */
/*
create database if not exists chantelle;
use chantelle;
-- set sql_mode='traditional';
create table if not exists test_time (
  z int,
  a time,
  b year,
  c date,
  d datetime,
  e timestamp
); 

insert into test_time(z) values (1);
insert into test_time(a) values ('23:59:59');
insert into test_time(b) values ('2008');
insert into test_time(b) values ('09');
insert into test_time(c) values ('2008-01-23');
insert into test_time(a) values ('23:59:59');
insert into test_time(d) values ('2008-01-23 23:59:59');
insert into test_time(e) values (NULL);
insert into test_time(d) values ('2008-01-23 23:59:59');
insert into test_time(a) values ('24:61:61'); -- error then stop
select * from test_time;

insert into test_time(c) values ('0000-00-00');
insert into test_time(c) values ('2008-02-31');


SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
-- datetime function:
-- ref:
--   http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
*/

/*************** mysql func() testing ***************/
/* sys */
/*
select version();
select database();
select user(), charset(user()), collation(user());
*/

/* date */
/*
select curdate(), current_date(), 
curtime(), current_time(),
now(), current_timestamp();
select concat(year(now()),'-',month(now()),'-',day(now()),' ',hour(now()),':',minute(now()),':',second(now()));

select date_format(now(),get_format(datetime,'internal'));
select date_format(now(),get_format(datetime,'eur'));
select date_format(now(),'%Y-%m-%d %H.%i.%s');
select date_format('2003-10-03 23:59:59',get_format(datetime,'eur'));
select str_to_date(now(),get_format(datetime,'iso')), str_to_date(date_format(now(),get_format(datetime,'eur')),get_format(datetime,'eur'));
select str_to_date('10.31.2003',get_format(date,'usa'));

select makedate(2008,200), maketime(8,6,20);
*/

/* null */
/*
select isnull(null), isnull(0), isnull(1), isnull('');
select ifnull(null,'is null'), ifnull(null,'is null');
select concat('a','b'), concat('a',null,'b');
select if(null,'true','false'), if(isnull(null),'true','false');
*/

/* math */
/*
select truncate(1/7,9), 1/7, round(1/7,4), truncate(1/7,4), ceiling(1/7), floor(1/7), abs(-1/7);
select sign(1/7), sign(-1/7), sign(0);
select sin(0), cos(0), tan(0);
select pi(), degrees(pi()), radians(180);
create table test_std (
  no int;
);
insert into t1 values (1),(2),(3);
select std(c1) from test_std;

## use with bulkInsert user defined function.
#A-Z, char(65)-char(90),
#a-z, char(97)-char(122),
truncate table t1;
truncate table t2;
call bulkInsert('t1','char(floor(rand()*26)+65),ceiling(rand()*27)',100);
call bulkInsert('t2','char(floor(rand()*26)+96),ceiling(rand()*27)',100);


*/

/* others */
/*
select repeat('*',20);
select least(1,3,-2,4,6,0), least('de','abc','x','a','yz','ghi');
select greatest(1,3,-2,4,6,0), greatest('de','abc','x','a','yz','ghi');
*/

/* flow control */
/*
select if(1>0,'yes','no');
select interval(5,2,3,7,8,3,2,0); -- ans. 2 => 7>5, then return the index of '3' (2);
*/

/* string */
/*
select instr('Alice and Bob', 'and'), locate('and', 'Alice and Bob'), position('and' in 'Alice and Bob');
select length('MySQL'), char_length('MySQL');
select length(convert('MySQL' using ucs2)), char_length(convert('MySQL' using ucs2));
select concat('to','see','is','to','believe');
select concat_ws(' ','to','see','is','to','believe');
select substring('George and Mary',0), substring('George and Mary',1), substring('George and Mary',8,3);
select substring('George and Mary',-4), substring('George and Mary',-8,3);
select substring_index('George and Mary','and',1), substring_index('George and Mary','and',0), substring_index('George and Mary','and',-1);
select substring_index('a b c d e',' ',1);  -- a
select substring_index('a b c d e',' ',2);  -- a b
select substring_index('a b c d e',' ',3);  -- a b c
select substring_index('a b c d e',' ',-1); -- e
select substring_index('a b c d e',' ',-2); -- d e
select substring_index('a b c d e',' ',-3); -- c d e
select left('George and Mary',6), right('George and Mary',4);
select concat('<',trim(' and '),'>'), concat('<',ltrim(' and '),'>'), concat('<',rtrim(' and '),'>');
select trim(leading '0' from '01');
select replace('01', '0', '');
select insert('2359', 3, 0, ':'), insert('23____59', 3, 4, ':'); -- insert before posi 3, and 0 chars to be replaced; insert before posi 3, and 4 chars to be replaced.
select strcmp('abc','def'), strcmp('def','def'), strcmp('ghi','def');
select 'abc'='def', 'def'='def', 'ghi'='def';
*/

/*************** mysql func() testing ***************/

/* 
-- drop database test; create database test;
-- drop database if exists test;
create database if not exists test;
use test;
create table test (
  c nchar(10) default null,
  a int default null
);

-- drop database joseph; create database joseph;
-- drop database if exists joseph;
create database if not exists joseph;
use joseph;
create table test (
  c nchar(10) default null,
  a int default null
);

insert into test.test values ('a',1);
insert into test.test values ('a',2);
insert into test.test values ('b',3);
insert into test.test values ('b',7);
insert into test.test values ('b',8);
insert into test.test values ('c',9);
insert into test.test values ('c',1);
insert into test.test values ('c',1);
insert into test.test values (NULL,NULL), (null,null);
select * from test.test;

insert into joseph.test values ('a',1);
insert into joseph.test values ('a',3);
insert into joseph.test values ('b',5);
insert into joseph.test values ('b',7);
insert into joseph.test values ('c',2);
insert into joseph.test values ('c',4);
insert into joseph.test values ('d',7);
insert into joseph.test values ('d',7);
insert into test.test values (NULL,NULL), (null,null);
select * from joseph.test;

select
  *
from
  test.test s,
  joseph.test  j
where s.a=j.a
order by 1
limit 0,5;
## limit not support sub-query or vairable.
##   mysql > select * from t limit ( select count(*) from t where i='a' );
##   mysql >
##   mysql > set @a=1;
##   mysql > select * from t limt @a;
 
select distinct
  *
from 
  test.test s,
  joseph.test  j
where s.a=j.a
order by 1
limit 0,5;

select
  c, a, count(1) -- count(1), psedo column with value 1; count(a) will lost count if the value of a is null.
from test
where 1=1
  and c is not null
  and a is not null
group by c,a
with rollup;

create database if not exists test;
create table test.fromTable (ino int);
insert into test.fromTable values (1),(2),(3);
create table test.toTable as select * from test.fromTable;
truncate table test.fromTable;
select * from test.fromTable;
insert into test.fromTable select * from test.toTable;
select * from test.fromTable;
select * from test.toTable;
*/

/* create database */
/*
create database test;
create database if exists test;
drop database test;
drop database if exists test;
create database Inno2 character set UTF8 collate UTF8_bin;

## test drop database fail...
create database dropDBFail;
system pfexec su - mysql -c "touch /usr/local/mysql/data/dropDBFail/dropDBFail.txt";
drop database dropDBFail;
show errors;
show databases like 'dropDBFail';
system pfexec su - mysql -c "rm usr/local/mysql/data/dropDBFail/dropDBFail.txt";
drop database dropDBFail;
show databases like 'dropDBFail';
*/

/* create table */
/*
drop database if exists test;
create database if not exists test;
use test;

-- enable MyISAM table to store checksum value, 
-- to let "mysql> checksum table table_name;" to be faster.
-- Otherwise, it needs to do a full table scan to checksum table.
create table checkSumTable ( no int )
engine=MyISAM checksum=1;

-- temporary table
create temporary table tmpTable ( no int ); # visible only on current session and will be dropped when connection is closed.
drop temporary table tmpTable;

drop table if exists student;
create table student (
  id int auto_increment,
  name varchar(32) unique,
  chi int not null,
  eng int not null,
  math int not null,
  primary key (id)
);
insert into student (name,chi,eng,math) values ('Tom',100,100,100);
insert into student values (10,'Mary',90,90,90);
insert into student (name,chi,eng,math) values ('John',80,80,80);
insert into student values (5,'christy',70,70,70);
insert into student (name,chi,eng,math) values ('Mike',60,60,60);
select * from student;
delete from student where id = 12;
insert into student (name,chi,eng,math) values ('David',50,50,50);
select * from student;

create table selectStudent as select * from student; -- create table with default engine, and also insert data.
create table likeStudent like student;               -- create table with the same engine.
alter table selectStudent add column location varchar(32) default 'Yilan' comment 'The location of students';
select * from selectStudent;
alter table selectStudent change location loc varchar(48); -- change column name and datatype.
alter table selectStudent modify loc varchar(32); -- change column datatype.
select * from selectStudent;
alter table selectStudent drop column loc;

alter table selectStudent rename to renameStudent;
rename table renameStudent to selectStudent;
rename table selectStudent to s, likeStudent to l;
select * from s,l;

-- eliminate duplicated data with create table as select
create table abc (i int);
insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL);
select * from abc;
create table cba
as select distinct * from abc;
drop table abc;
rename table cba to abc;

-- row format, for MyISAM engine
use test;

# create dynamic row 
create table dynamicRow(id int, name varchar(32)) engine=MyISAM row_format=dynamic;
call bulkInsert('dynamicRow','rand()*100,repeat("a",rand()*32)',100);
show table status like 'dynamicRow';

# create fixed row 
create table fixedRow(id int, name varchar(32)) engine=MyISAM row_format=fixed;
insert into fixedRow select * from dynamicRow;
show table status like 'fixedRow';

# create compressed table for dynamic row
create table compressedDynamicRow(id int, name varchar(32)) engine=MyISAM row_format=dynamic;
insert into compressedDynamicRow select * from dynamicRow;
system pfexec myisampack $MySQLDataDir/test/compressedDynamicRow;
system pfexec myisamchk -rq $MySQLDataDir/test/compressedDynamicRow;

# create compressed table for fixed row
create table compressedFixedRow(id int, name varchar(32)) engine=MyISAM row_format=fixed;
insert into compressedFixedRow select * from dynamicRow;
system pfexec myisampack $MySQLDataDir/test/compressedFixedRow;
system pfexec myisamchk -rq $MySQLDataDir/test/compressedFixedRow;

*/


/* primary key */
/*
drop database if exists test;
create database if not exists test;
use test;

create table t1 (
  id int auto_increment,
  no int,
  primary key (id)
);
create table t2 (
  id int auto_increment,
  no int
);
alter table t2 add primary key (id);

-- how to modify primary key: a. drop old one; b. create new one;
-- drop primary key: alter table table_name drop primary key;


insert into t1 (no) values (1),(2),(3);
insert into t2 (no) values (1),(2),(3);

explain t1\G
desc t1\G
explain t2\G
desc t2\G
explain select * from t1 where id=1;
desc select * from t1 where id=1;
explain select * from t1 where no=1;
desc select * from t1 where no=1;
*/


/* foreign key */
/*
mysql> set foreign_key_checks=0; -- It's useful while need to manage data and ignore constraints.
mysql> set unique_checks=0; -- It's useful while need to enhance performance to load mass data. -- unique check => check constraints row by row.


mysql> create table a(id int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> create table b(id int, p int, foreign key(p) a(id)) engine=innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a(id)) engine=innodb' at line 1
mysql> create table b(id int, p int) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> alter table b add foreign key(p) references a(id);
ERROR 1005 (HY000): Can't create table 'test.#sql-5f3_3' (errno: 150)
mysql> show errors;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Error | 1005 | Can't create table 'test.#sql-5f3_3' (errno: 150) |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
=> 150, source column must be indexed!

mysql> alter table a add key(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table b add foreign key(p) references a(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

 */
/*
drop database if exists test;
create database if not exists test;
use test;

create table country (
  code int not null auto_increment,
  name varchar(32) not null,
  sname char(2) not null,
  primary key (code)
) engine=InnoDB; -- InnoDB implement the foreign key.

create table nPop (
  pop int not null,
  primary key (pop)
) engine=InnoDB; -- InnoDB implement the foreign key.

create table sCityName (
  no int not null auto_increment,
  name varchar(32) unique,
  primary key (no)
) engine=InnoDB;

create table city (
  id int not null auto_increment,
  name varchar(32) not null,
  cc int not null,
  pop int default 0,
  primary key (id),
  foreign key (name)
    references sCityName (name)
    on delete no action,
  foreign key (cc)
    references country (code)
    on delete cascade,
  foreign key (pop)
    references nPop (pop)
    on delete set null
) engine=InnoDB; -- InnoDB implement the foreign key.

-- how to modify foreign key: a. drop old one; b. create new on;
-- drop foreign key: alter table table_name drop foreign key foreign_key_name;

insert into country values \
 (null, 'Taiwan', 'TW') \
,(null, 'Japan', 'JP') \
,(null, 'China', 'CN') \
,(null, 'America', 'US') \
,(null, 'England', 'UK') \
,(null, 'French', 'FR');

insert into nPop values \
(100),(200),(300),(400),(500),(600);

insert into sCityName (name) \
values('Taipei'),('Tokyo'),('NewYork'),('Tainan'),('Washington'),('Paris');

insert into city values (null, 'Taipei', 1, 200);
insert into city values (null, 'Tokyo', 2, 400);
insert into city values (null, 'NewYork', 4, 600);
insert into city values (null, 'Tainan', 10, 200); // out of country code
insert into city values (null, 'Washington', 20, 800); // out of country code, nPop
insert into city values (null, 'Paris', 6, 1000); //out of country code, nPop,

select * from country;
select * from nPop;
select * from city;

delete from country where name='Taiwan';
delete from nPop where pop=400;
delete from sCityName where name='NewYork';

select * from country;
select * from nPop;
select * from city;

truncate table nPop;
select * from country;
select * from nPop;
select * from city;
*/

/* delete */
/*
drop database if exists test;
create database if not exists test;
use test;
create table delTable (
  no int
);
insert into delTable values (1),(1),(2),(2),(3),(3),(4),(4);
select * from delTable;
delete from delTable where no=1 limit 1; -- delete from delTable order by no limit 1;
delete from delTable where no=2 limit 1;
delete from delTable where no=3 limit 1;
delete from delTable where no=4 limit 1; -- delete from delTable order by no desc limit 1;
select * from delTable;
*/

/* update */
/*
drop database if exists test;
create database if not exists test;
use test;
create table people (
  id int not null,
  name varchar(32),
  age int,
  primary key (id)
);
insert into people values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
update people set id=id-1;                  -- success.
select * from people;
update people set id=id+1;                  -- fail, when 1+1=2, 2 already exists, pk constraint error.
select * from people;
update people set id=id+1 order by id desc; -- success.
select * from people;

## update multi-table
use test;
drop table if exists t1,t2;
create table t1(n varchar(32),i int);
create table t2(n varchar(32),i int);
insert into t1 values('a',2);
insert into t1 values('b',4);
insert into t1 values('c',6);
insert into t2 values('a',7);
insert into t2 values('b',8);
insert into t2 values('d',9);
#
update t1 inner join t2 on t1.n=t2.n set t1.i=1, t2.i=1 where t1.n='a';
# update t1 inner join t2 using(n) set t1.i=1, t2.i=1 where t1.n='a'; # the same
select * from t1;
select * from t2;
*/

/* replace */
/*
drop database if exists test;
create database if not exists test;
use test;
create table people (
  id int not null,
  name varchar(32),
  age int,
  count int default 1,
  primary key (id)
);
insert into people (id,name,age) values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
replace into people (id,name,age) values (2,'V2',25);
select * from people;
replace into people (id,name,age) values (5,'V3',28);
select * from people;
create table people2 as select * from people;
update people2 set id=id+10;
replace into people select * from people2;

insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;

select * from people;
*/


/* union */
/*
drop database if exists test;
create database if not exists test;
create table test_union1 (
  id int increment,
  no in
);
create table test_union2 (
  id int increment,
  no in
);
insert into test_union(no) values (1),(1),(2),(3),(4),(4),(5),(6),(7),(8),(9);
insert into test_union(no) values (2),(3),(8),(1),(4),(4),(5),(0),(0),(0),(0);
select * from t1 union selecct * from t2;
select * from t1 unionall selecct * from t2;
select * from t1 minus select * from t2; -- not supported
*/


/* transaction */
/*
-- save point
set @@autocommit=0;
drop database if exists test;
create database test;
use test;
create table a (
  no int 
) engine=InnoDB;

start transaction;
  insert into a values (1);
  select * from a;
  savepoint s1;

  insert into a values (2);
  select * from a;
  savepoint s2;

  insert into a values (3);
  select * from a;
  savepoint s3;

  rollback to savepoint s2;
  select * from a;

  rollback;
  select * from a;
commit;

-- transaction
create table people (
  id int not null,
  name varchar(32),
  age int,
  count int default 1,
  primary key (id)
) engine=InnoDB;

---- show engines information.
show engines;
create database if not exits test_engine;
use test_engine;
create table t1 (
  id int
) engine=MEMORY;
create table t2 (
  id int
);
create temporary table t3 (
  id int
) engine=MEMORY;
create temporary table t4 (
  id int
);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
insert into t3 values (1),(2),(3);
insert into t4 values (1),(2),(3);

---- set current autocommit off.
set autocommit = off;
set session autocommit = off;
set @@autocommit := 0;

---- start mysqld with default autocommit off.
---try ...
-- shell>mysqld -u root init-connect="set autocommit := off"
-- or modify my.conf ( not work for root )

insert into people (id,name,age) values (1,'Tom',10);
select * from people;
rollback;
select * from people;

start transaction;
insert into people (id,name,age) values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
rollback;
select * from people;
*/

/* join */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
  id int auto_increment,
  no int,
  name varchar(32),
  primary key (id)
);
create table t2 (
  id int auto_increment,
  no int,
  addr varchar(32),
  primary key (id)
);

insert into t1 (no, name) values (1,'Tom'),(2,'Joseph'),(3,'Chantelle'),(4,'Christy');
insert into t2 (no, addr) values (1,'Taipei'),(2,'Taizhong'),(3,'Yilan'),(5,'Tainan');

-- caresian product.
select * from t1,t2;
select * from t1 cross join t2;

-- inner join.
select * from t1 join t2 on t1.no=t2.no;
select * from t1 inner join t2 using (no);
select * from t1,t2 where ( select no from t2 where t1.no=t2.no ) = ( select no from t1 where t2.no=t1.no);
select * from t1 inner join t2 on t1.no < t2.no;
select * from t1 inner join t2 on t1.id between t1.no and t2.no;

-- outer join.
select * from t1 left join t2 on t1.no=t2.no;  -- major in left (must show left list), and join from right.
select * from t1 left outer join t2 on t1.no=t2.no;  -- major in left (must show left list), and join from right.
select * from t1 right join t2 on t1.no=t2.no; -- major in right (must show right list), and join from left.
select * from t1 right outer join t2 on t1.no=t2.no; -- major in right (must show right list), and join from left.

-- joins in update and delete statements.
update t1
inner join t2
on t1.no = t2.no
set t1.id=t1.id+10,
    t2.id=t2.id+100; -- can update t2, so rare!

select * from t1;
select * from t2;
select * from t1 join t2 using (no);

select * from t1 where exists (select null from t2 where t1.no=t2.no);
select * from t1 where t1.no in (select no from t2);
select * from t1 where t1.no = any (select no from t2);
select * from t1 where t1.no = some (select no from t2);

## test natural join
drop table if exists i,j,k;
create table t1(i int,j int,k int);
create table t2(j int,k int,l int);
insert into t1 values (1,2,3),(1,2,4);
insert into t2 values (2,3,7),(2,8,9);
select * from t1 natural join t2;
# => select * from t1 inner join t2 using(j,k);
# => select j, k, i, l from t1, t2 where t1.j=t2.j and t1.k=t2.k; 
# result:
# +------+------+------+------+
# | j    | k    | i    | l    |
# +------+------+------+------+
# |    2 |    3 |    1 |    7 |
# +------+------+------+------+
# 1 row in set (0.00 sec)

## test cross join
select * from t1 cross join t2;
# result: 
# +------+------+------+------+------+------+
# | i    | j    | k    | j    | k    | l    |
# +------+------+------+------+------+------+
# |    1 |    2 |    3 |    2 |    3 |    7 | 
# |    1 |    2 |    4 |    2 |    3 |    7 | 
# |    1 |    2 |    3 |    2 |    8 |    9 | 
# |    1 |    2 |    4 |    2 |    8 |    9 | 
# +------+------+------+------+------+------+
# 4 rows in set (0.00 sec)

*/

/* view */
/*
-- cannot link trigger on view.
drop database if exists test;
create database if not exists test;
use test;
create table city (
  id int auto_increment,
  name varchar(32),
  pop int,
  primary key (id)
);
create table cityMayer (
  cityName varchar(32),
  mayerName varchar(32),
  primary key (cityName)
);
insert into city (name, pop) values ('Taipei',300);
insert into city (name, pop) values ('Tainan',200);
insert into city (name, pop) values ('Yilan',100);
insert into cityMayer values ('Taipei','John');
insert into cityMayer values ('Tainan','Mary');
insert into cityMayer values ('Taizhong','Tom');

-- create view.
create or replace view cityView (city, mayer, pop) as
select name, mayerName, pop
from city, cityMayer
where 1=1
  and city.name=cityMayer.cityName
  and pop > 100
with check option;

-- select view.
select * from cityView;

---- update view.
-- update cityView
-- set city='TaipeiCity'
-- where city='Taipei';
update cityView
set pop=10;
select * from city; -- update fail with check option.
update cityView
set pop=1000;
select * from city; -- success.

-- but how to create readonly view?????, grant read pri to user.

select * from cityMayer;
select * from cityView;


## test view with create/replace.
create view v as select * from m; # table m exists
drop table m;
show create view v;                     # view exist
create or replace v as select * from n; # table n exists
create or replace v as select * from t; # table t doesnot exist
show create view v;                     # view exist with old syntax


## with cascaded/local check option;
drop table if exists testCheckOption;
create table testCheckOption( t int );
create or replace view v1 as select * from testCheckOption where t<10;
create or replace view v2 as select * from v1 where t>0 with cascaded check option;
create or replace view v3 as select * from v1 where t>0 with local check option;
insert into v1 values(10); # success
insert into v2 values(20); # fail, even v1 doesnot have check option, v2 will cascaded checking v1 where cause.
insert into v3 values(30); # success
select * from testCheckOption;
select * from v1;
select * from v2;
select * from v3;


## priv
drop table if exists testViewPriv;
create table testViewPriv( n int );
create definer='christy'@'localhost' sql security definer view v_testViewPriv as select * from testViewPriv;
grant update on stanley.testViewPriv to 'christy'@'localhost';
grant update on stanley.v_testViewPriv to 'chantelle'@'localhost';
insert into testViewPriv values (1),(2),(3);

drop table if exists testViewPriv2;
create table testViewPriv2( n int );
create sql security invoker view v_testViewPriv2 as select * from testViewPriv2;
grant update on stanley.testViewPriv2 to 'chantelle'@'localhost';
grant update on stanley.v_testViewPriv2 to 'chantelle'@'localhost';
insert into testViewPriv2 values (1),(2),(3);
*/

/* prepare statement */
/*
-- prepare statement is session base.
drop database if exists test;
create database if not exists test;
use test;
create table city (
  pop int
);
insert into city values (100),(200),(300),(400),(500);

prepare my_stmt from 'select * from city where pop > ?';
set @pop=200;
execute my_stmt using @pop;
deallocate prepare my_stmt;
*/

/* export/import file */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t (
  id int auto_increment,
  no int,
  primary key (id)
);
insert into t (no) values (1), (2), (3), (4), (5), (6);
select * from test.t;
select * into outfile '/tmp/data/t.txt'
fields
  terminated by ','
  enclosed by '"'
  lines terminated by '\n'
from test.t; -- default in /opt/mysql/mysql/data, if file exists then error return, how to overwrite it?????, no solution.... 

truncate table t;
load data infile '/tmp/data/t.txt'
into table t
fields
  terminated by ','
  enclosed by '"'
  lines terminated by '\n';
select * from t;
*/

/*
shell>
mysqldump -uroot test   > /tmp/mysql/data/test.sql
mysqldump -uroot test t > /tmp/mysql/data/test.t.sql
mysqldump -d -uroot test t > /tmp/mysql/data/test.t.schema_only.sql
mysql -u root < /tmp/mysql/data/test.sql
mysql -u root < /tmp/mysql/data/test.t.sql

shell>
mysql -u root <<EOF
truncate table test.t;
EOF
mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' \
            --lines-terminated-by="\n" --ignore \
            test /tmp/data/t.txt 
## '--ignore/--replace' contain unique key values already in the table, 'test' is database name, 't' is the tablename.
*/



/* stored routines */
/*
## change stored routine characteristics, (3 characters)
## 1. SQL SECURITY:
##    alter procedure p sql security [definer/invoker];
## 2. Data Use:
##    alter procedure p [contains sql/no sql/reads sql data/modifies sql data];
## 3. COMMENT:
##    alter procedure p comment 'This is comment...'
##
## create sample:
delimiter //
## cannot use the syntax: create procedure if not exists p()..., will fail!
drop procedure if exists p//
create procedure p()
no sql
sql security invoker
comment 'hello'
begin
  select 'NULL';
end//
delimiter ;

*/

/* pattern of delimiter. */
/*
delimiter //
create procedure/function ...
begin
  ...
end//
delimiter ;
*/

/*
delimiter ;
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
  id int auto_increment,
  no int,
  primary key (id)
);
create table t2 (
  id int auto_increment,
  no int,
  primary key (id)
);
insert into t1 (no) values (1),(2),(2);
insert into t2 (no) values (1),(1),(2),(2),(3),(3);

delimiter //
create procedure test_t_count(out nReturn int, inout nIO int, in no2 int)
begin
  select @nSes, nReturn, nIO, @no2 as no1;
  if no2 = 1
  then
    select count(1) from test.t1;
  elseif no2 = 2
  then
    select count(1) from test.t2;
  else
    select 'not match any case';
  end if;
  set @nSes=4, nReturn=5, nIO=6, no2=33;
end//
delimiter ;

-- test for update data with cursor
drop procedure if exists t;
delimiter //
create procedure t()
begin
  declare done1 int default 0;
  declare fet int default 0;
  declare c cursor for select * from test.n;
  declare continue handler for not found set done1=1;
  open c;
  lr:repeat
    fetch c into fet;
    if not done1 then
      update test.n set n=fet+10 where n=fet;
    end if;
  until done1 end repeat;
  close c;
end//
delimiter ;
drop procedure t;

-- test for use procedure to delete function/procedure, create succ, but call fail...
drop procedure if exists t;
delimiter //
drop procedure if exists t;
create procedure t()
begin
  declare done1 int default 0;
  declare db1 varchar(32) default '';
  declare name1 varchar(32) default '';
  declare type1 varchar(32) default '';
  declare c cursor for select db,name,type from mysql.proc;
  declare continue handler for not found set done1=1;
  set @cmd2=''; -- must put after declare.
  open c;
  lr:repeat
    fetch c into db1,name1,type1;
    if not done1 then
      select concat('drop ',type1,' ',db1,'.',name1) into @cmd2;
      prepare my_stmt from @cmd2; -- drop function/procedure not support prepare statement yet.
      execute my_stmt; -- prepare fail, so never reach this line
    end if;
  until done1 end repeat;
  close c;
end//
delimiter ;
call t();
drop procedure t;

## call bulkInsert('m','1,1,1',0);            # unlimited insert
## call bulkInsert('m','1,1,1',100);          # insert 100 recoreds
## call bulkInsert('m','NULL,NULL,NULL',100); # insert 100 recoreds for all NULL
## call bulkInsert('test_partition_np','NULL,NULL,date_add(\'1995-01-01\', interval ceiling(rand()*5843) day)',1000000); ## sample

delimiter //
drop procedure if exists bulkInsert//
CREATE PROCEDURE bulkInsert(in sTableName varchar(32), in sValues varchar(256), in nCounts int)
begin
  declare i int default 0;
  #declare null_not_allowed condition for sqlstate '23000';
  #declare null_not_allowed condition for 1048;
  #declare continue handler for null_not_allowed begin end;
  #declare continue handler for sqlstate '23000' begin end;
  #declare continue handler for 1048 begin end;

  #set @sPrepare='';
  select concat('insert into ',sTableName,' values(',sValues,')') into @sPrepare;
  #select @sPrepare;
  prepare sCmd from @sPrepare;
  repeat
    execute sCmd;
    set i=i+1;
    -- select i as counter;
  until i=nCounts end repeat;
end//
delimiter ;

## call bulkInsert2('m','id',0,0);            # unlimited insert
## call bulkInsert2('m','id',1,100);          # insert 100 recoreds, begin from 1
delimiter //
drop procedure if exists bulkInsert2//
CREATE PROCEDURE bulkInsert2(in sTableName varchar(32), in sColumn varchar(256), in nBegin int, in nCounts int)
begin
  declare i int default 0; 
  #declare null_not_allowed condition for sqlstate '23000';
  #declare null_not_allowed condition for 1048;
  #declare continue handler for null_not_allowed begin end;
  #declare continue handler for sqlstate '23000' begin end;
  #declare continue handler for 1048 begin end;

  foo:loop
    if i>=nCounts then
      leave foo;
    else
      set @sPrepare='';
      select concat('insert into ',sTableName,'(',sColumn,') values(',nBegin,')') into @sPrepare;
      prepare sCmd from @sPrepare;
      execute sCmd;
      set i=i+1;
      set nBegin=nBegin+1;
      -- select i as counter;
    end if;
  end loop;
end//
delimiter ;


## test recursive procedure
## mysql> set @@max_sp_recursion_depth=1000;
## mysql> set @maxLoop=100;
## mysql> call recursivePorcedure(@maxLoop);
delimiter //
drop procedure if exists recursiveProcedure;
create procedure recursiveProcedure(inout n int)
begin
  #select 'a' into @a;
  ## select * from t;            # can select
  ## call t();                   # can call another procedure
  ## call recursiveProcedure(n); # can call resursive procedure, but have to set @@max_sp_recursion_depth first, default=0
.

  select n;
  set n=n-1;

  if n > 0 then
    call recursiveProcedure(n);
  end if;

  #if n=0 then
  # return; #exit; #quit; #leave; ## not working, how to quit procedure??????
  #end if;
  #
  #The generic way to exit a block is the LEAVE statement:
  #create procedure myproc()
  #MAIN_BLOCK: begin
  #  declare v_panic bool default false
  #  ..
  #  ..
  #  if v_panic then
  #    leave MAIN_BLOCK;
  #  end if;
  #  ..
  #  ..
  #end MAIN_BLOCK;   

end//
delimiter ;



show create procedure test.test_t_count;
show procedure status;
show procedure status where Db='test';

drop procedure if exists test_t_count; 

set @nSes=1, @nReturn=2, @nIO=3, @no1=11, @no2=22;
select @nSes, @nReturn, @nIO, @no1, @no2;
call test_t_count(@nReturn, @nIO, @no1);
select @nSes, @nReturn, @nIO, @no1, @no2;

delimiter //


create function table_count(column_no int)
returns int
deterministic
begin
  declare nReturn int default 0;
  declare nCount1 int default 0;
  declare nCount2 int default 0;

  if column_no = 1
  then
    select count(distinct id) into nCount1 from t1;
    select count(distinct id) into nCount2 from t2;
  elseif column_no = 2
  then
    select count(distinct no) into nCount1 from t1;
    select count(distinct no) into nCount2 from t2;
  else
    set nCount1=0;
    set nCount2=0;
  end if;

  set nReturn=nCount1+nCount2;
  set @nT=nReturn;
  return @nT;
  -- return nCount1+nCount2;
  -- return nReturn;
end//

## function can call function, insert/update/delete table
## when use binlog, have to declare the function modify data or not, (use "NO SQL") or set global log_bin_trust_function_creators=1 (default 0)
delimiter //
drop function if exists upTable//
create function upTable()
returns int
deterministic
begin
  insert into m values (2);
  return 0;
end//
delimiter ;

delimiter //
drop function if exists seTable//
create function seTable()
returns int
no sql
deterministic
begin
  declare nReturn int default 0;
  select count(1) into nReturn from m;
  return nReturn;
end//

create function seTable(i int) ## create fail, function name exist!
returns int
no sql
deterministic
begin
  declare nReturn int default 0;
  select count(1) into nReturn from m;
  return nReturn;
end//
delimiter ;

delimiter //
drop function if exists t;
create function t()
returns int
deterministic
begin
  set @n=1; -- set @n
  return 2;
  set @m=3; -- never reach here.
end//
delimiter ;


set @nT=0;
select table_count(3);
select table_count(2);
select table_count(1);
select @nT;
set @nT=null;

show create function test.table_count;
show function status;
show function status where Db='test';

drop function if exists table_count; 

*/

/* flow control */
/*
delimiter ;
create database if not exists test;
use test;
*/

/* if */
/*

## select if
select if(i=0,0,1) from m;

drop function if exists testIF;
delimiter //
create function testIF(n int)
returns varchar(32)
deterministic
begin
  if n=1
  then
    return 'true';
  else
    return 'false';
  end if;
end//
delimiter ;
select testIF(0);
select testIF(1);
drop function testIF;
*/

/* case */
/*
## select case...
select
case month when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
end # not 'end case' here, when use select case ...
from calendar where year = "2005" order by month;


drop function if exists testCase;
delimiter //
create function testCase1(n int)
returns varchar(32)
no sql
deterministic
begin
  case n
  when 1 then
    return '1';
  when 2 then
    return '2';
  else
    return 'no match';
  end case;
end//
delimiter ;
select testCase1(1);
select testCase1(2);
select testCase1(3);
drop function testCase1;

drop function if exists testCase2;
delimiter //
create function testCase2(n int)
returns varchar(32)
deterministic
begin
  case
  when n = 1 then
    return '1';
  when n = 2  then
    return '2';
  else
    return 'not match';
  end case;
end//
delimiter ;
select testCase2(1);
select testCase2(2);
select testCase2(3);
drop function testCase2;
*/

/* repeat */
/*
drop function if exists testRepeat;
delimiter //
create function testRepeat(n int)
returns int
deterministic
begin
  declare nCount1 int default 0;
  declare nCount2 int default 0;
  repeat
    set nCount1 = nCount1 + 1;
    set nCount2 = nCount1 * nCount1;
  -- until nCount1 >= n -- cannot left ';' here.
  -- end repeat;
  until nCount1 >= n end repeat; -- best practice.
  return nCount2;
end//

delimiter ;
select testRepeat(1);
select testRepeat(2);
select testRepeat(3);
drop function testRepeat;
*/

/* while */
/*
drop function if exists testWhile;
delimiter //
create function testWhile(n int)
returns int
deterministic
begin
  declare nCount int default 1;
  while nCount < n
  do
    set nCount = nCount + 1;
  end while;
  return nCount;
end//
delimiter ;
select testWhile(1);
select testWhile(2);
select testWhile(3);
drop function testWhile;
*/

/* loop */
/*
drop function if exists testLoop;
delimiter //
create function testLoop(n int)
returns int
deterministic
begin
  declare nCount int default 1;
  -- loop usually within label, and use leave label to exit loop.
  mylabel:loop
    if nCount >= n
    then
      leave mylabel;
    else
      set nCount=nCount+1;
      iterate mylabel;
    end if;
    -- never reach here...
    set nCount=1000;
  end loop mylabel;
  return nCount;
end//
delimiter ;
select testLoop(1);
select testLoop(2);
select testLoop(3);
drop function testLoop;
*/

/* condition & handler */
/*
drop database if exists test;
create database test;
use test;
create table d_table (
  n int,
  primary key (n)
);
delimiter //
create procedure dohandler(in n int, out x int)
begin
  declare nCount int default 1;                         -- variable and condition declaration must before cursor and handler.
  declare dup_keys condition for sqlstate '23000';      -- variable and condition declaration must before cursor and handler.
  -- declare c cursor for select * from test.testCursor1; -- must declare before handler -- sample only
  -- declare continue handler for not found set done=1; -- sample only
  declare continue handler for dup_keys set @garbage=1; -- declare handler for contidion.
  truncate table test.d_table;
  set @garbage=0;
  while nCount <= n
  do
    set x = nCount;
    insert into test.d_table values (1);
    set nCount = nCount + 1;
  end while;
end//
delimiter ;
call dohandler(1,@x);
select @garbage, @x;
call dohandler(2,@x);
select @garbage, @x;
call dohandler(3,@x);
select @garbage, @x;
drop procedure dohandler;
*/

/* cursor */
/*
drop database if exists test;
create database test;
use test;
create table test.testCursor1 (
  no int
);
create table test.testCursor2 like test.testCursor1;
insert into test.testCursor1 values (1),(2),(3),(4),(5);
delimiter //

## test cursor scope.
drop procedure if exists testCursorScope//
create procedure testCursorScope()
begin
  begin
    declare dup_key condition for sqlstate '23000';
    declare c cursor for select * from test.m;
    declare continue handler for dup_key set @x=1;
    open c;
  end;
  begin
    declare n int default 0;
    fetch c into n;
  end;
end//
## result::
#mysql> source /tmp/t.sql
#Query OK, 0 rows affected, 1 warning (0.00 sec)
#ERROR 1324 (42000): Undefined CURSOR: c


## test cursor scope2.
## nothing to see, because open cursor not lock table? it's rare...
drop procedure if exists testCursorScope2//
create procedure testCursorScope2()
begin
  begin
    declare i int default 0;
    declare j int default 0;
    declare not_null_allowed condition for sqlstate '23000';
    declare c cursor for select * from test.m;
    declare d cursor for select * from test.n;
    declare continue handler for not_null_allowed set @x=1;

    ## case 1:
    open c;
    fetch c into i;
    open d;
    fetch d into j;
    select "case 1:";
    select "use following command to check table status:";
    select "  show open tables;";
    select "  flush tables;";
    select "  show open tables;";
    select sleep(20);

    ## case 2:
    close c;
    select "case 2:";
    select "use following command to check table status:";
    select "  flush tables;";
    select "  show open tables;";
    select sleep(20);
  end;

  ## case 3:
  select "case 3:";
  select "use following command to check table status:";
  select "  flush tables;";
  select "  show open tables;";
  select sleep(20);
end//

## test cursor insensitive
## steps:
## 1. create procedure testCursorInsensitive.
##    > copy and paste with following procedure.
## 2. create table m with values 1,2,3.
##    > create table m (i int); insert into m values(1),(2),(3); select * from m; # => 1,2,3
## 3. call procedure testCursorInsensitive.
##    > call testCursorInsensitive();
## 4. open another session and modify data.
##    > insert into m values(7); delete from m where i=2; update m set i=0 where i=1; select * from m; # => 0,3,7
## 5. wait the procedure return.
##    => 1,2,3
## Result:
##    The cursor does not see the change after the cursor is opened.

delimiter // 
drop procedure if exists testCursorInsensitive//
create procedure testCursorInsensitive()
begin
  declare v int default NULL;
  declare done int default 0;
  declare dup_key condition for sqlstate '23000';
  declare my_not_found condition for sqlstate '02000';
  declare c cursor for select * from test.m;
  declare continue handler for dup_key begin end;
  declare continue handler for not found set done=1;    # shorthand
  declare continue handler for my_not_found set done=1; # with my declare condition
  open c;
  select sleep(20);
  repeat
    fetch c into v;
    if done != 1
    then
      select v;
    end if;
  until done = 1 end repeat;
  close c;
end//
delimiter ;


delimiter //
drop procedure if exists testCursor//
create procedure testCursor() 
begin
  declare done int default 0;
  declare n int default 0;
  declare c cursor for select * from test.testCursor1; -- must declare before handler
  declare continue handler for not found set done=1;
  open c;
  repeat
    fetch c into n;
    if not done then
      insert into test.testCursor2 values (n);
      -- if n=3 then
      --   set done=1;
      -- end if;
    end if;
  until done end repeat;
  close c;
end//
delimiter ;

call testCursor();
select t1.no t1, t2.no t2
from test.testCursor1 t1
     left join testCursor2 t2
     on t1.no=t2.no;
drop table testCursor1;
drop table testCursor2;
drop procedure testCursor;
*/

/* trigger */
/*
drop database if exists test;
create database if not exists test;
use test;

create table t1 (
  id int auto_increment,
  name varchar(32),
  primary key (id)
);

create table i1 (
  id int,
  name varchar(32),
  primary key (id)
);

create table d1 (
  id int,
  name varchar(32),
  primary key (id)
);

create table u1 (
  old_id int,
  old_name varchar(32),
  new_id int,
  new_name varchar(32)
);

-- insert trigger
create trigger t1_i1
after insert
on t1
for each row
insert into i1 values (new.id, new.name);

-- delete trigger
create trigger t1_d1
after delete
on t1
for each row
insert into d1 values (old.id, old.name);

-- update trigger
create trigger t1_u1
after update
on t1
for each row
insert into u1 values(old.id, old.name, new.id, new.name);

-- PS. the foreign key with cascade action, not active trigger.
insert into t1 (name) values ('Tom'),('John'),('Mary');
update t1 set id=id+10;
delete from t1;

select * from t1;
select * from i1;
select * from d1;
select * from u1;



-- table declaration
drop table if exists p;
create table if not exists p (id int, no int);
drop table if exists c;
create table if not exists c (cid int, pid int, name varchar(32));
drop table if exists old_p;
create table if not exists old_p(id int, no int);
drop table if exists old_c;
create table if not exists old_c(cid int, pid int, name varchar(32));
-- end table declaration

-- trigger declaration
delimiter //

-- create succ, but run fail, ERROR 1442 (HY000): Can't update table 'p' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
--
-- drop trigger if exists ai_p//
-- create trigger ai_p after insert on p
-- for each row
-- begin
--   if new.id is null then
--     delete from p where id is null;
--   end if;
-- end//

drop trigger if exists ad_p//
create trigger ad_p after delete on p
for each row
begin
  insert into old_p values (old.id, old.no);
  delete from c where pid=old.id;
end//

drop trigger if exits ad_c//
create trigger ad_c after delete on c
for each row
begin
  insert into old_c values (old.cid, old.pid, old.name);
end//

insert into p values (1,1),(2,2),(3,3);
insert into c values (1,1,'tom'),(2,2,'joseph'),(3,3,'christy'),(4,4,'chantelle');

delimiter ;
-- end trigger declaration


*/

/* lock table */
/*
set sql_mode='';
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
  no int
);
create table t2 (
  no int
);
create table t3 (
  no int
);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
insert into t3 values (1),(2),(3);
lock tables t1 write, t2 read, t3 write, t3 as t3_4r read;

select * from t1; -- success
insert into t1 values (4),(5),(6); -- success
select * from t1; -- success

select * from t2; -- success
insert into t2 values (4),(5),(6); -- fail. if run command by stdin, will exit program.
select * from t2; -- success

select * from t3;
insert into t3 select * from t3;
select * from t3;
insert into t3 select * from t3 as t3_4r;
select * from t3;
unlock tables;
*/

/* cache */
/*
use test;
set global test_cache.key_buffer_size=128*1024;
cache index t1,t2 in test_cache;
load index into cache t1,t2;
load index into cache t1,t2 ignore leaves;
set global test_cache.key_buffer_size=0;
*/

/* partitiion table */
/*
-- partitions must be integer or trsnfer to integer by func.
-- supports MyISAM, InnoDB, Archive storage engines.
show variables like '%have_partitioning%';
drop database if exists test;
create database if not exists test;
use test;

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

create table tl (
  id int
) engine=MyISAM
  partition by list(id) 
  (
    partition p0 values in (0,2,4,6,8),
    partition p1 values in (1,3,5,7,9)
  );

create table tk1 (
  id int,
  tr_date date,
  primary key (id)
) engine=InnoDB
  partition by key()
  partitions 10;

create table tk2 (
  id int not null, -- must have not null
  tr_date date,
  unique key (id)
) engine=InnoDB
  partition by key()
  partitions 10;

create table tk3 (
  id int,
  tr_date date,
  primary key (id)
) engine=InnoDB
  partition by key(id)
  partitions 10;

create table tk4 (
  id int,
  tr_date date
) engine=InnoDB
  partition by linear key(id)
  partitions 10;

explain partition select * from tk4 where id=1;

-- range
drop table tr1;
create table tr1 (
  id int
) engine = InnoDB
  partition by range (id) 
  (
    partition p0 values less than (10),
    partition p1 values less than (20),
    partition pm values less than (maxvalue)
  );

drop table tr2;
create table tr2 (
  id int
) engine = InnoDB
  partition by range (id) 
  (
    partition p0 values less than (10),
    partition p1 values less than (20)
  );

mysql> insert into tr2 values (30);
ERROR 1526 (HY000): Table has no partition for value 30
mysql>

## convert from partition table to normal table
alter table tr2 remove partitioning;
## convert from normal table to partition table
alter table tr2 
  partition by range (id) 
  (
    partition p0 values less than (10),
    partition p1 values less than (20)
  );
## drop partition data.
alter table tr2 drop partition p0;
*/
*/

/*
-- subpartition
drop table ts1;
create table ts1 (
  id int,
  no int
) engine=InnoDB
  partition by list (mod(id,2))
  subpartition by hash (no)
  subpartitions 2 (
    partition p0 values in (0),
    partition p1 values in (1)
  );

drop table ts2;
CREATE TABLE ts2 (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) 
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN (MAXVALUE)
        (
            SUBPARTITION s4,
            SUBPARTITION s5
--            ,SUBPARTITION s6 -- error with not mismatch sub partitions
        )
    )
    ;
*/

/* storage engine merge */
/*
########################### ??? create merge fail, recheck again...
## can use a mix of compressed and uncompressed tables.
#drop database if exists test;
create database if not exists test;
use test;
drop table t1,t2,t3,total1,total2;
CREATE TABLE t1 (
  a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(20)
) ENGINE=MyISAM;
CREATE TABLE t2 (
  a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(20)
) ENGINE=MyISAM;
CREATE TABLE t3 (
  a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(20)
) ENGINE=MyISAM;
call bulkInsert('t1','NULL,\"t1\"',1000);
call bulkInsert('t2','NULL,\"t2\"',1000);
call bulkInsert('t3','NULL,\"t3\"',1000);
#INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
#INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
#INSERT INTO t3 (message) VALUES ('Testing'),('table'),('t3');

system pfexec myisampack $MySQLDataDir/test/t3
CREATE TABLE total1 (
  a INT NOT NULL AUTO_INCREMENT,
  message CHAR(20),
  INDEX(a)
) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
CREATE TABLE total2 (
  a INT NOT NULL AUTO_INCREMENT,
  message CHAR(20),
  INDEX(a)
) ENGINE=MERGE UNION=(t1,t3) INSERT_METHOD=LAST;

select * from total1;
select * from total2;
##ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist


INSERT INTO total1 (message) VALUES ('Testing'),('table'),('total1');
INSERT INTO total2 (message) VALUES ('Testing'),('table'),('total2');



*/


/* case sensitive */
/*
drop database if exists test;
create database if not exists test;
use test;
create table case_cs_test (
  word VARCHAR(10)
) CHARACTER SET latin1 COLLATE latin1_general_cs; -- use _cs to case sensitive.
insert into case_cs_test values ('A'),('a'),('B'),('b'),('C'),('c');
select *
from case_cs_test 
where word rlike '[A-Z]';
*/

/* create user */
/*
-- if joseph not exists, create it.
-- if joseph exists, change his password to 'new_password'.
create user joseph@localhost identified by 'new_password';
grant select on world.* to joseph@localhost identified by '123';
grant all on test.* to 'joseph'@'localhost' identified by '123'
with
max_user_connections 1234
max_connections_per_hour 1235
max_queries_per_hour 1236
max_updates_per_hour 1237;
select * from world.City;
revoke select on world.* from joseph@localhost;

-- create user by clone someone priv.
drop user 'joe'@'localhost';
system rm /tmp/t.sql; ## \! rm /tmp/t.sql;
select * into outfile '/tmp/t.sql' from mysql.user where user='stanley' and host='localhost';
system perl -pi -e "s/localhost\tstanley/localhost\tjoe/" /tmp/t.sql;
load data infile '/tmp/t.sql' into table mysql.user;

*/

/* federated engine */
/*
-- not support by default, must start mysqld with option --federated
drop database if exists world;
create database if not exists world;
use world;

CREATE TABLE City (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
) ENGINE=InnoDB;

grant select on world.City to 'bob'@'localhost' identified by '123';

CREATE TABLE FedCity (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (ID)
) ENGINE=FEDERATED
CONNECTION='mysql://bob:123@localhost/world/City';

insert into City(Name,CountryCode,District,Population) values ('Tom','TW','Taipei',100);
insert into City(Name,CountryCode,District,Population) values ('Joseph','TW','Taipei',100);
insert into City(Name,CountryCode,District,Population) values ('Christy','TW','Taipei',100);
*/


/* create user */
/*
## create user with clear text password
create user bob identified by '123';
create user joseph@localhost identified by '123';

## update password with clear text
set password for 'bob'@'localhost' = password('789');
set password = password('abc'); -- set the password of non-anonymous client.
update mysql.user set password=password('456') where user='bob';

## update password with encypted text
set password for 'stanley'@'localhost' = '*2FCDC0F13666DF2E09612610C2987BE092AB473D'; -- set the password with encrypted password by command "set password".
update mysql.user set password='*2FCDC0F13666DF2E09612610C2987BE092AB473D' where user='stanley' and host='localhost'; -- set the password with encrypted password by update "mysql.user" data table.


###################################### priv_types:
## 1. table priv_type:
##    alter, create view, create, delete, drop, grant option, index, insert, select, show view, and update
grant all on *.* to 'bob'@'%'; -- auto create user. use sql_mod='no_auto_create_user' to avoid auto create user.
grant all on *.* to 'bob'@'%' identified by 'abc'; -- although use sql_mod='no_auto_create_user', still create user. (***)
grant all on *.* to 'bob'@'localhost';
grant select on *.* to 'bob'@'localhost';
grant all on mysql.* to 'bob'@'localhost';
grant select on mysql.* to 'bob'@'localhost';
grant all on mysql.user to 'bob'@'localhost';
grant select on mysql.user to 'bob'@'localhost';
##
## 1.x show grants:
show grants for 'bob'@'localhost';

## 2. column priv_type:
##    insert, select, and update
grant select (col1), insert (col1, col2) on mysql.user to 'bob'@'localhost';

## 3. routine priv_type:
##    alter routine, create routine, execute, and grant option
grant create routine on mysql.* to 'bob'@'localhost';
grant execute on procedure on mysql.proc to 'bob'@'localhost';

## 4. with grant option:
grant select on world.* to 'bob'@'localhost' with grant option;
##
## 4.x revoke with grant option:
## revoke grant option on world.* from 'bob'@'localhost';

############ security.
## over ssl
grant all privileges on test.* to 'bob'@'localhost'
  identified by 'bob123' require ssl;

## limit login host
#grant all privileges on test.* to 'bob'@
#1. '%'
#2. '%.sun.com'
#3. '192.168.1.0/255.255.255.0'
#3. '192.168.1._' -- .0~.9

## limit connect, query per hour
grant all on world.*
to 'bob'@'localhost'
with 
max_user_connections 1
max_connections_per_hour 10
max_queries_per_hour 50
max_updates_per_hour 20;

show grants;
show grants for current_user();

## check user
ex.
mysql> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| bob@a.b.c.d    | bob@%          | 
+----------------+----------------+

drop user test;
*/

/* create index */
/*
create table ttt (
  id int,
  no int,
  grade int,
  name varchar(32),
  index idx_ttt_id (id) -- key idx_ttt_id(id)
) engine=MyISAM;
create index idx_ttt_id on ttt(id);
create index idx_ttt_no on ttt(no);
create index idx_ttt_grade on ttt(grade);
create index idx_per_name on ttt(name(10)); -- create prefix index.
create index idx_pre_name1 on ttt(left(name,10)); -- not work, not support functional index.
create index idx_pre_name2 on ttt(name(10)); -- only use the index with conditon name='test'; not left(name,8)='test.
show index from ttt;
-- using index ( like oracle hint )
SELECT * FROM ttt USE INDEX (idx_ttt_id,idx_ttt_no)
  WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM ttt IGNORE INDEX (idx_ttt_grade)
  WHERE col1=1 AND col2=2 AND col3=3;

# syntax: 
#   create index index_name type on table_name(columns);
#   alter table table_name add index type(columns);
# type:
#   using btree|hash|rtree
create index hash1 using hash on t(i,j);
alter table t add index using hash(i,j);

drop table if exists testIndex;
create table testIndex(i int, j int, index idx1(i,j), index idx2(j));
insert into testIndex values (1,1),(2,2);
## use index
explain select * from testIndex use index (idx1)\G
explain select * from testIndex use index (idx2)\G
explain select * from testIndex use index (idx1,idx2)\G

## ignore index
explain select * from testIndex ignore index (idx1)\G
## ignore index is equivalent the combination of hints:
## 1. explain select * from testIndex ignore index for join (idx1)\G
## 2. explain select * from testIndex ignore index for order by (idx1)\G
## 3. explain select * from testIndex ignore index for group by (idx1)\G
explain select * from testIndex ignore index (idx2)\G
explain select * from testIndex ignore index (idx1,idx2)\G

## force index
explain select * from testIndex force index (idx1)\G
##However, it is an error to mix USE INDEX and FORCE INDEX for the same table:
##SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);


-- predict prefix
create table t ( name varchar(32));
insert into t values ('this'),('is'),('a'),('dog'),('that'),('is'),('a'),('cat'),('good'),('morning'),('mr'),('li'),('good'),('morning'),('good'),('afternoon'),('good'),('evening'),('good'),('night');

select
  count(*) as 'Total Rows',
  count(distinct name) as 'Distinct Values',
  count(*) - count(distinct name) as 'Duplicate Values'
from t;

set @n=2;
select
  count(distinct left(name,@n)) as 'Distinct Prefix Values',
  count(*) - count(distinct left(name,@n)) as 'Duplicate Prefix Values'
from t;


-- fulltext index
create table fulltext_sample (
  id int,
  name varchar(8),
  addr varchar(32)
);
alter table fulltext_sample add fulltext fulltext_name(name);
#alter table t3 add fulltext tf(i);
select * from fulltext_sample where match(name) against('tom');

-- special index
create table lookup (id int) engine=memory;
create index idx_lookup_id using btree on lookup(id);
*/

/* max rows */
/*
## specifying MyISAM Maximum Row Count.
## Note that, despite the name MAX_ROWS does not enforce a maximun number of rows for table. The option value is only used to calculate the pointer size. Not force, but also no warning!!
## [mysqld]
## myisam_data_pointer_size=7 ## default: 6

## case 1:
## the fixed-length is not need avg_row_length.
create table t1(id int) engine=MyISAM max_rows=2; 
alter table t1 max_rows=4;
insert into t1 values (1),(2),(3),(4),(5);

select * from t;
## case 2:
create table t2(name varchar(32) engine=MyISAM avg_row_length=16 max_rows=4000000;
alter table t2 avg_row_length=24 max_rows=3000000;
*/

/* table maintenance */
/*
check table t;    -- for MyISAM, InnoDB. also update index status and view definition.
repair table t;   -- for MyISAM only.
analyze table t;  -- for MyISAM, InnoDB. analyze the key values of the table.
optimize table t; -- for MyISAM, InnoDB. clean up MyISAM by defragment, sort index pages, update index statistics. update InnoDB index statistics and free space in the clustered index.
checksum table t; -- for MyISAM, ???
*/

/* profiling */
/*
select @@profiling;
set profiling=1;
select * from mysq.user;
create database test_profiling;
use test_profiling;
create table p1 ( name varchar(32));
insert into p1 values ('Tom'),('Christy');
select * from p1 where name='Tom';
--
show profile;
show profiles; 
show profile for query 1; -- get query id from show profiles.
show profile cpu for query 1;
--
set profiling=0;
*/


/* dangerous */
/*
create database if not exist test;
use test;
create table if not exist aInt ( n int );
create table if not exist bVar ( n varchar(2));
insert into aInt values (1),(2),(3);
insert into bVar values ('1a'),('2b'),('3c');
select * from aInt a, bVar b where a.n=b.n;
set sql_mode=oracle;      -- not work.
select * from aInt a, bVar b where a.n=b.n;
set sql_mode=traditional; -- not work. 
*/

/* tee */
/*
tee /tmp/tee.log;
select * from information_schema.tables
where table_name=user;
notee;
*/

/* group_concat */
/*
select 'call group_concat.................';
create database if not exists test;
use test;
create table if not exists testGroupConcat (i int, v varchar(32));
insert into testGroupConcat values (1,'a'),(1,'b'),(2,'z'),(2,'y'),(2,'x'),(3,'i'),(3,NULL),(3,'j'),(NULL,'p'),(NULL,'q'),(NULL,'r');
select * from testGroupConcat;
select i, group_concat(v) from testGroupConcat;
select i, group_concat(v order by (v)) from testGroupConcat;
select i, group_concat('-', v order by (v)) from testGroupConcat;
select i, group_concat('-', v order by (v) separator ':') from testGroupConcat;
*/

/* purge binary log */
/*
## purge binary logs
purge binary logs to 'mysql-bin.010';
purge binary logs before '2009-05-31 23:59:59';
*/

/* functions */
/*
## sleep
select sleep(10); # sleep 10 sec, then return 0.
*/

/* manipulating table data */
/*
## update multi-tables
update m inner join n using(i)
set m.i=4, n.i=4;
## delete multi-tables
delete m,n from m,n where m.i=n.i;       # success deleting from m,n
delete from m,n using m,n where m.i=n.i; # success deleting from m,n
delete from m inner join n using(i);     # success deleting from m,n
delete m,n from m join n on m.i=n.i;     # success deleting from m,n 
delete m from n where m.i=n.i;           # ERROR 1109 (42S02): Unknown table 'm' in MULTI DELETE
delete t1,t3 from t1,t2,t3 
where  t1.id=t2.id and t3.id=t2.id;      # success deleting from t1,t3
delete t1,t3 from t1
inner join t2 using(id)
inner join t3 using(id);                 # success deleting from t1,t3


use test;
drop table if exists gc;
drop table if exists c;
drop table if exists p;
create table p (id int primary key) engine=innodb;
create table c (id int primary key, pid int, foreign key (pid) references p(id)) engine=innodb;
create table gc (id int primary key, pid int, foreign key (pid) references c(id)) engine=innodb;
insert into p values (1),(2);
insert into c values (1,1),(2,1),(3,2);
insert into gc values (1,1),(2,1),(3,1),(4,2),(5,2),(6,3);
############################################### all delete methods fail...
delete p,c,gc
from p
  inner join c on c.pid = p.id
  inner join gc on gc.pid = c.id
;
delete gc,c,p
from p
  inner join c on c.pid = p.id
  inner join gc on gc.pid = c.id
;
delete gc,c,p
from gc
  inner join c on gc.pid = c.id
  inner join p on c.pid = p.id
;
############################################### all delete methods fail...
*/

/* replication */
/*
## refer to test_createReplication.txt
start/stop slave;
start/stop slave io_thread;
start/stop slave sql_thread;

## for my.cnf
####################################################### replication
## @master server, set bin log database
#binlog-do-db=db1
#binlog-do-db=db2
#binlog-do-db=db3
## @slaver server1, set replicated database
#replicate-do-db=db1
#replicate-do-db=db2
## @slaver server2, set replicated database
#replicate-do-db=db2
#replicate-do-db=db3

*/
/* event */
/*
set global event_scheduler=on;  # default is off
grant event on stanley.* to 'bob'@'localhost'; 
create
definer='stanley'@'localhost'   # defualt is CURRENT_USER
event if not exists event_name
on schedule every 10 second
on completion preserve          # default is not preserve
enable                          # default is enable, enable/disable/disable on slave
comment 'put comments here'
do
select now() into outfile '/tmp/now.txt'
*/

/* grant */
/*
## the grant target is signature, so the target doesnot have to exist.
## ex. table ttt.yyy doesnot exist. but still can be granted,
##     except funcion/procedure.
##     mysql> grant all on ttt.yyy to 'guest'@'localhost';
##     mysql> grant all on function db.func_must_exist to 'guest'@'localhost';
##
## grant/revoke all with grant option.
## ex.
##     mysql> grant all on *.* to 'guest'@'localhost' with grant option;
##     mysql> revoke all, grant option on *.* from 'guest'@'localhost';
## 

## user level
#grant all          on *.* to 'guest'@'localhost';
grant select,update on *.* to 'guest'@'localhost';
select * 
from mysql.user 
where 1=1
  and user='guest' 
  and host='localhost'
  and select_priv='y'
  and update_priv='y'\G

## database level
#grant all          on world.* to 'guest'@'localhost';
grant select,update on world.* to 'guest'@'localhost';
select * 
from mysql.db 
where 1=1 
  and user='guest' 
  and host='localhost' 
  and db='world'
  and select_priv='y'
  and update_priv='y'\G

## table level
#grant all          on world.City to 'guest'@'localhost';
grant select,update on world.City to 'guest'@'localhost';
select * 
from mysql.tables_priv 
where 1=1 
  and user='guest' 
  and host='localhost' 
  and db='world'
  and table_priv = 'select,update'\G

## column level, will also update mysql.tables_priv
grant select(ID,Name), update(Name) on world.City to 'guest'@'localhost';
select * 
from mysql.columns_priv 
where 1=1 
  and user='guest' 
  and host='localhost' 
  and db='world' 
  and table_name='City' 
  and (
        (column_name='ID'   and column_priv='select') or
        (column_name='Name' and column_priv='select,update')
      )\G

select * 
from mysql.tables_priv 
where 1=1 
  and user='guest' 
  and host='localhost' 
  and db='world'
  and column_priv = 'select,update'\G

## proc level
delimiter //
drop procedure if exists world.testProc;
create procedure world.testProc()
no sql
begin
  select '1';
end//
drop function if exists world.testFunc;
create function world.testFunc()
returns int
no sql
deterministic
begin
  return 1;
end//
delimiter ;
#grant all     on procedure world.testProc to 'guest'@'localhost';
grant execute on procedure world.testProc to 'guest'@'localhost';
#grant alter   on procedure world.testProc to 'guest'@'localhost';
grant all     on function  world.testFunc to 'guest'@'localhost';
#grant execute on function  world.testFunc to 'guest'@'localhost';
#grant alter   on function  world.testFunc to 'guest'@'localhost';
select *
from mysql.procs_priv
where 1=1
  and user='guest'
  and host='localhost'
  and db='world'
  and (
        (routine_name='testProc' and routine_type='PROCEDURE' and proc_priv='execute') or
        (routine_name='testFunc' and routine_type='FUNCTION' and proc_priv='execute,alter routine')
      )\G 
## the column "routine_type" is case sensitive,
## the column "proc_priv" value is
##   'execute' if only execute priv
##   'alter routine' if only alter priv

        

## drop user and also delete above priv data.
drop user 'guest'@'localhost';
select * from mysql.user where user='guest';
select * from mysql.db where user='guest';
select * from mysql.tables_priv where user='guest';
select * from mysql.columns_priv where user='guest';
select * from mysql.procs_priv where user='guest';
*/

/* change user password */
/*
grant usage on *.* to 'guest'@'localhost' identified by 'new_password';
grant usage on *.* to 'guest'@'localhost' identified by password '*0913BF2E2CE20CE21BFB1961AF124D4920458E5F'; # *0913... ( encrypted password )
set password for 'guest'@'localhost' = password ('new_password');
update mysql.user set password=password('new_password');
*/

/* status */
/*
## connection information.
show status like 'Connections';
show status like 'Aborted_connects';
show status like 'Aborted_clients';
show status like 'Threads_connected';
##
## 1. Connections
## 2. Aborted_connects
## 3. Connections - Aborted_connects
## 4. Aborted_clients
## 5. Connections - Aborted_connects - Aborted_clients
## 6. Threads_connected
##

## Com variables give you a breakdown of the number of statements 
## that the server has executed by statement type.
show global status like 'Com%';

## open tables;
show status like 'Opened_tables';

## amount of traffic
show status like 'Bytes_received';
show status like 'Bytes_sent';


## server load
## case 1: Questions/Uptime, how many queries per second the server has processed.
show global status like 'Questions';
show global status like 'Uptime';
## case2: slow queries
show global status like 'slow_queries';

## case3: show process
show processlist;
*/

/* select */
/*
# default elements: 256, default memory: 8192
select * from table_name procedure analyse(max_elements, [max_memory]);
*/

/* character set */
/*
set names utf8;
CREATE TABLE a ( a VARCHAR(32)) CHARACTER SET='BIG5' COLLATE='BIG5_BIN'; 
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;


*/

/* connection, kill session */
/*
kill connection 5;
*/

/*
import data skip foreign key constraint check:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;
 */

Wish this helps.
regards,
Stanley Huang

[Level 3] Benchmark in MySQL

2012-01-16T00:47:00.001+08:00

#!/usr/bin/bash
#mysqlslap -a -c100 i10 -u root -p --engine=InnoDB,MyISAM,MEMORY,ARCHIVE
mysqlslap -a --only-print

## ref.@@pseudo_thread_id to get different values of thread.
# mysqlslap --create="CREATE TABLE A (a int);INSERT INTO A values (@@pseudo_thread_id)"
# --query="SELECT * FROM A" --concurrency=50 --iterations=200

Wish this helps.
regards,
Stanley Huang

[Level 3] Transaction in MySQL.

2012-01-16T00:45:00.001+08:00

#!/usr/bin/bash -vx
MYSQL_HOME=/opt/mysql/mysql
mysql -uroot -proot_pwd <<EOF
create database if not exists test;
use test;
create table if not exists test (id int) engine=InnoDB;
alter table test engine=Innodb;
truncate table test;
insert into test values (1),(2),(3);

system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.bak

start transaction;
insert into test values (4);
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran1;
rollback;
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.rollback;
system diff $MYSQL_HOME/data/test/test.ibd.tran1 $MYSQL_HOME/data/test/test.ibd.rollback > ./diff_tran1_rollback;

start transaction;
insert into test values (4);
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.tran2
commit;
system cp $MYSQL_HOME/data/test/test.ibd $MYSQL_HOME/data/test/test.ibd.commit
system diff $MYSQL_HOME/data/test/test.ibd.tran2 $MYSQL_HOME/data/test/test.ibd.commit > ./diff_tran2_commit

EOF

Wish this helps.
regards,
Stanley Huang

[Level 3] XML in MySQL.

2012-01-16T00:42:00.002+08:00

#!/usr/bin/bash
createTable() {
  mysql -ujoseph <<EOF
CREATE TABLE persons (
id int auto_increment primary key,
data text
);
EOF
}
#createTable

createUsers(){
  mysql -ujoseph <<EOF
INSERT INTO persons(data) values 
('
<person>
  <name>stanley</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12340001</tel>
    <tel>12340002</tel>
    <tel>12340003</tel>
  </tels>
</person>
'),
('
<person>
  <name>joseph</name>
  <sex>m</sex>
  <addr>taipei</addr>
  <tels>
    <tel>12350001</tel>
    <tel>12350002</tel>
    <tel>12350003</tel>
  </tels>
</person>
')
;
EOF
}
#createUsers

queryUserTels() {
  mysql -ujoseph <<EOF
select
ExtractValue(data,'//person/tels/tel[1]'),
ExtractValue(data,'/person/tels/tel[2]'),
ExtractValue(data,'person/tels/tel[3]')
from persons
where id=1;
EOF
}
#queryUserTels

updateXML() {
  mysql -ujoseph <<EOF
select
data,
ExtractValue(data,'//person/name'),
UpdateXML(data,'//person/name','<name>christy</name>'),
ExtractValue(UpdateXML(data,'//person/name','<name>christy</name>'),'//person/name')
from persons
where id=1;
EOF
}
#updateXML

Wish this helps. regards, Stanley Huang

[Level 2] How to delete duplicate data in MySQL.

2012-01-16T00:37:00.002+08:00

drop table if exists abc;
create table abc (i int DEFAULT NULL);
insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL);
-- insert into abc values (),(),(),(); -- work
-- insert into abc values (1),(),(),(),(); -- not work
-- ERROR 1136 (21S01): Column count doesn't match value count at row 2

-- case 1:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
-- case 2:
delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);

/* result:
mysql> -- case 1:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL) limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1' at line 1
mysql> -- case 2:
mysql> delete abc from abc, (select * from abc group by i having count(*)>1) as xyz where abc.i=xyz.i or (abc.i is NULL and xyz.i is NULL);
Query OK, 9 rows affected (0.00 sec)
*/


/*
easy way to delete duplicate data.
create table cba as select distinct from abc;
drop table abc;
rename table cba to abc;
*/

Wish this helps.
regards,
Stanley Huang

[Level 2] mysqlshow notes for MySQL.

2012-01-16T00:34:00.002+08:00

#!/usr/bin/bash -vx
read -p "Enter Password: " p
c="mysqlshow -uroot -p$p"
case1() {
  c1="$c"
  c2="$c1 mysql"
  c3="$c2 user"
  c4="$c3 host"
  echo $c1 && $c1
  echo $c2 && $c2
  echo $c3 && $c3
  echo $c4 && $c4
}

case2() {
  c1="$c"
  c2="$c1 m%"
  c3="$c1 mysql t%"
  c4="$c1 mysql time_zone_transition Tr%"
  echo $c1 && $c1
  echo $c2 && $c2
  echo $c3 && $c3
  echo $c4 && $c4
}

case2

Wish this helps.
regards,
Stanley Huang

[Level 3] mysqldump/mysqlimport notes for MySQL.

2012-01-16T00:32:00.000+08:00

#!/usr/bin/bash
#dData=`dirname $0`/data
dData=/tmp/data
mkdir -p $dData && cd $dData

mysqldump -uroot test > $dData/test.sql
mysqldump -uroot test t > $dData/test.t.sql

#mysql -uroot < $dData/test.sql
#mysql -uroot < $dData/test.t.sql

exit
exit
exit

--routines
--triggers

#!/usr/bin/bash
#dData=`dirname $0`/data
dData=/tmp/data
mkdir -p $dData
cd $dData

mysql -uroot <<EOF
use test;
truncate table t;
select * from t;
EOF

mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' \
            --lines-terminated-by="\n", --ignore \
            test $dData/t.txt 
## '--ignore/--replace' contain unique key values already in the table, 'test' is database name, 't' is the tablename.

Wish this helps. regards, Stanley Huang

[Level 3] Notes for MySQL replication

2012-01-16T00:28:00.002+08:00

#################################################### master server
set server_id=0; ## master server_id must be 0
grant replication slave on *.* to 'slave'@'%' identified by 'slave';
show master status;
# +-----------------------+----------+--------------+------------------+
# | File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
# +-----------------------+----------+--------------+------------------+
# | Stanley-NB-bin.000103 |      377 |              |                  | 
# +-----------------------+----------+--------------+------------------+

## in my.cnf
[mysqld]
server-id=0


#################################################### slaver server
set server_id=1;
# shell> mysqldump -uroot -p test t1 > /tmp/t.sql
# mysql> create database if not exist test;
# mysql> use test;
# mysql> source /tmp/t.sql
change master to
  master_host='192.168.1.100',
  master_user='slave',
  master_password='slave',
  master_port=3306,
  master_log_file='Stanley-NB-bin.000103',
  master_log_pos=337;

## in my.cnf
[mysqld]
server-id=1
master-host=192.168.1.100
master-user=slave
master-password=slave
master-port=3306

#################################################### command
start/stop slave;
start/stop slave io_thread;
start/stop slave sql_thread;

Wish this helps.
regards,
Stanley Huang

[Level 2] CSV storage engine testing for MySQL.

2012-01-16T00:26:00.000+08:00

For testing CSV storage engine for MySQL,
I wrote a testing script as the following.

#!/usr/bin/bash -vx
cat > /tmp/test_csv.CSV <<EOF
1,"Tom",90,90,80
2,"Stanley",80,100,100
3,"Joseph",70,80,90
4,"Christy",80,80,100
5,"Chantelle",60,60,60
EOF
mysql -uroot -p -e "create table test_csv ( no int not null, name varchar(32) not null, chi int not null, eng int not null, math int not null) engine=CSV;" world
cp /tmp/test_csv.CSV /var/lib/mysql/world/test_csv.CSV
mysql -uroot -p -e "flush table test_csv; select * from test_csv;" world

Wish this helps.
regards,
Stanley Huang

[Level 2] Packing MyISAM data table into readonly.

2012-01-16T00:21:00.001+08:00

/*
step 1: LOCK TABLE x FOR WRITE;
step 2: FLUSH TABLE x;
step 3: myisamchk -cFU -- fast check for pre-existing errors. Don't pack if errors exist. Ignore the warning of table "not closed" because this check will go ahead and closed the DB files.
step 4: myisampack -f -- force overwrite of any preexisting .TMD file
step 5: myisamchk -raqS -- rebuild the index after pack
step 6: FLUSH TABLE x; -- force reload of info_schema data
step 7: UNLOCK TABLES; -- Release the table.
*/

mysql -u mysql <<EOF
use test;
lock table x for write;
flush table x;
EOF

cd /opt/mysql/mysql/data/test;
myisamchk -cFU x;
mysiampack -f x;
myisamchk -raqS x;

mysql -u mysql <<EOF
flush table x;
unlock tables;
EOF

Wish this helps. regards, Stanley Huang

[Level 3] Multithread in Python

2012-01-12T20:58:00.000+08:00

#!/bin/env python
import os, sys
import threading
import random

class ThreadClass (threading.Thread):
    def __init__(self, function, seconds, threads):
        self.function = function
        self.seconds = seconds
        threading.Thread.__init__(self)
        threads.append(self)
     
    def run(self):
        print 'run %s...' % self.seconds
        self.function(self.seconds)
        print 'run %s done!' % self.seconds

class TestMultiThread(object):
    def __init__(self):
        self.max_thread = 5 
        self.threads = []
        pass

    def task(self, seconds):
        os.popen('sleep %s' % seconds).read()

    def run(self):
        for i in range(0, self.max_thread):
            seconds = int(random.random() * 10) 
            ThreadClass(self.task, seconds, self.threads).start()

        for t in self.threads:
            t.join()

        print 'Jobs done!'

if __name__ == '__main__':
    tmt = TestMultiThread()
    tmt.run()

$ ./testMultiThread.py 
run 1...
run 9...
run 2...
run 7...
run 1...
run 1 done!
run 1 done!
run 2 done!
run 7 done!
run 9 done!
Jobs done!
$

Wish this helps. regards, Stanley Huang

[Level 2] Flyweight in Python.

2012-01-09T22:44:00.000+08:00

#!/usr/bin/env python
import random

class Black(object):
    __instance = None
    def __new__(cls):
        if not cls.__instance:
            cls.__instance = super(Black, cls).__new__(cls)
        return cls.__instance

    def __str__(self):
        return 'black'

class White(object):
    __instance = None
    def __new__(cls):
        if not cls.__instance:
            cls.__instance = super(White, cls).__new__(cls)
        return cls.__instance

    def __str__(self):
        return 'white'

class BlackAndWhite(object):
    __black = Black()
    __white = White()
    def __new__(cls, index):
        if index == 0:
            return cls.__black
        else:
            return cls.__white

    def __init__(self, index):
        pass

class Board(object):
    def __init__(self, size):
        self.__size = size
        self.__planet = [ [ None for i in range(self.__size) ] for j in range(self.__size) ]

    def setPlanet(self):
        while True:
            x = int(random.random() * self.__size)
            y = int(random.random() * self.__size)
            if self.__planet[x][y] != None:
                continue
            else:
                z = int(random.random() * 2)
                self.__planet[x][y] = BlackAndWhite(z)

            if self.isCompleted():
                break

    def isCompleted(self):
        for x in range(0, self.__size):
            for y in range(0, self.__size):
                if self.__planet[x][y] == None:
                    return False
        return True

    def getPlanet(self):
        self.setPlanet()
        return self.__planet

    def displayPlanet(self):
        print [ [str(j) for j in i ] for i in self.getPlanet() ]

if __name__ == '__main__':
    b = Board(4)
    b.displayPlanet()
    pass

$ ./testFlyweight.py
[['black', 'white', 'black', 'black'], ['white', 'black', 'white', 'black'], ['black', 'black', 'black', 'black'], ['white', 'black', 'white', 'white']]
$

Wish this helps. regards, Stanley Huang

[Level 2] Singleton in Python.

2012-01-09T21:39:00.005+08:00

#!/usr/bin/env python
class Singleton(object):
    _instance = None
    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super(Singleton, cls).__new__(cls, *args, **kwargs)
        return cls._instance

print me
print you 

print me == you        
print id(me) == id(you)

$ ./testSingleton.py
True
True
$

You could also use metaclass to implement it.

#!/usr/bin/env python
class Singleton(type):
    def __init__(self, *args, **kwargs):
        super(Singleton, self).__init__(*args, **kwargs)
        self.__instance = None

    def __call__(self, *args, **kwargs):
        if self.__instance is None:
            self.__instance = super(Singleton, self).__call__(*args, **kwargs)
        return self.__instance

class s(object):
    __metaclass__ = Singleton
    def __init__(self, size):
        self.__size = size

    def getSize(self):
        return self.__size

class n(object):
    def __init__(self, size):
        self.__size = size

    def getSize(self):
        return self.__size

a = s(1)
print a.getSize()
b = s(2)
print b.getSize()

c = n(3)
print c.getSize()
d = n(4)
print d.getSize()

print a == b
print c == d

$ ./testMetaclassSingleton.py
1
1
3
4
True
False
$

in Python 2.6 or later, you could use decorator to implement Singleton:

#!/bin/env python
def singleton(cls):
    instances = {}
    def getInstance(*args, **kwargs):
        if cls not in instances:
            instances[cls] = cls(*args, **kwargs)
        return instances[cls]
    return getInstance

@singleton
class s(object):
    def __init__(self, size):
        self.__size = size

    def getSize(self):
        return self.__size

class n(object):
    def __init__(self, size):
        self.__size = size

    def getSize(self):
        return self.__size

a = s(1)
print a.getSize()
b = s(2)
print b.getSize()

c = n(3)
print c.getSize()
d = n(4)
print d.getSize()

print a == b
print c == d

Wish this helps.
regards,
Stanley Huang

[Level 1] How to evaluate the string to boolean?

2012-01-09T10:46:00.002+08:00

If you want to evaluate the string you could create a function like below.

#!/bin/env python
def toBoolean(str):
    return if str in ['True', '1']

print toBoolean('True')
print toBoolean('False')

But you could have an easier way to implement with native module called "ast.literal_eval()"
e.g.

>>> import ast
>>> help(ast.literal_eval)
Help on function literal_eval in module ast:

literal_eval(node_or_string)
    Safely evaluate an expression node or a string containing a Python
    expression.  The string or node provided may only consist of the following
    Python literal structures: strings, numbers, tuples, lists, dicts, booleans,
    and None.
>>> ast.literal_eval('False')
False
>>> ast.literal_eval('True')
True
>>> ast.literal_eval('1')
1
>>> ast.literal_eval('0')
0

Wish this helps. regards, Stanley Huang

[Level 2] Juniper vpn for ubuntu

2012-01-03T21:03:00.000+08:00

If you need to setup the vpn on Ubuntu, you could refer to the following 2 links:

http://http://mad-scientist.us/juniper.html
http://holyarmy.org/2009/06/vpn-on-ubuntu-linux-with-juniper-network-connect/

Wish this helps.
regards,
Stanley Huang

[Level 2] Package management in Python. (pip)

2011-12-13T22:05:00.000+08:00

pip is a tool for installing and managing Python packages,
you could find more info here

Wish this helps.
regards,
Stanley Huang

[Level 2] MySQL security check with tcpdump.

2011-11-29T00:53:00.000+08:00

you can check if MySQL data streams are encrypted or not by the following command:

# tcpdump -l -i eth0 -w - src or dst port 3306 | strings

Wish this helps.
regards,
Stanley Huang

[Level 3] Pythonbrew, the Python environment management tool.

2011-11-22T15:06:00.002+08:00

The more info, please click here

The Chinese resource from OpenFoundry, click here.

Wish this helps.
regards,
Stanley Huang

[Level 3] Slides from Devoxx talk on Language / Library / VM Co-Evolution.

2011-11-21T14:21:00.001+08:00

Slides from Devoxx talk on Language / Library / VM Co-Evolution,
more info, please click here.

Wish this helps.
regards,
Stanley Huang

[Level 3] The bonding info of Linux

2011-11-21T14:16:00.000+08:00

The bonding info of Linux, click here for more detail.

Wish this helps.
regards,
Stanley Huang

[Level 2] Pydoc, the document of Python.

2011-11-18T16:20:00.001+08:00

In Java, we have javadoc to generate the document,
in Python, we have pydoc.
More info, please click here.

The sample of pydoc as the following:

The source code of testPyDoc.py

#!/usr/bin/python

"""
This is a sample module to generate pydoc
"""
__author__ =  'Stanley Huang'
__version__=  '1.0'

class PyDoc:
    """class docstrings"""
    def __init__ (self, user='guest', addr='Taipei'):
        """Set default attribute values only
        Keyword arguments:
        user -- name of user
        addr -- addr of user
        """
        self.user = user
        self.addr = addr

$ pydoc testPydoc
Help on module testPyDoc:

NAME
    testPyDoc - This is a sample module to generate pydoc

FILE
    /tmp/testPyDoc.py

CLASSES
    PyDoc
    
    class PyDoc
     |  class docstrings
     |  
     |  Methods defined here:
     |  
     |  __init__(self, user='guest', addr='Taipei')
     |      Set default attribute values only
     |      Keyword arguments:
     |      user -- name of user
     |      addr -- addr of user

DATA
    __author__ = 'Stanley Huang'
    __version__ = '1.0'

VERSION
    1.0

AUTHOR
    Stanley Huang

Wish this helps. regards, Stanley Huang

[Level 3] How to implement AES in Python.

2011-11-18T12:16:00.001+08:00

A paper for how to implement AES in Python. Please click here.

Another paper is here.

Wish this helps.
regards,
Stanley Huang

[Level 3] IoC/AOP in Python.

2011-11-16T23:22:00.001+08:00

More Ioc info, please refer to here.
More AOP info, please refer to here.
Wish this helps.
regards,
Stanley Huang

[Level 2] How to update Ubuntu boot menu (grub).

2011-11-16T12:41:00.007+08:00

If you want to modify grub menu of Ubuntu,
you have following steps to do:
1. Modify default grub configuration file.
# vi /etc/default/grub
2. Update grub configuration.
# update-grub
Generating grub.cfg ...
Found linux image: /boot/vmlinuz-2.6.38-12-generic
Found initrd image: /boot/initrd.img-2.6.38-12-generic
Found linux image: /boot/vmlinuz-2.6.38-8-generic
Found initrd image: /boot/initrd.img-2.6.38-8-generic
Found memtest86+ image: /boot/memtest86+.bin
Found Windows Recovery Environment (loader) on /dev/sda1
Found Windows 7 (loader) on /dev/sda2
done
#

More info. please refer to here.

Wish this helps.
regards,
Stanley Huang

[Level 2] classmethod, staticmethod, abstractmethod decorators in Python.

2011-11-11T14:18:00.002+08:00

#!/bin/env python
from abc import *

class p(object):
    __metaclass__ = ABCMeta

    @abstractmethod
    def printz(self):
        pass

    @abstractproperty
    def x(self):
        pass

class o(p):
    def __init__(self, n=10):
        self._x = n 

    def printX(self):
        print self._x

    def printx(self):
        print self.x

    @classmethod
    def printy(self):
        print self.x

    @staticmethod
    def printz():
        print 'x' 

    @property
    def x(self):
        return self._x

    @x.setter
    def x(self, times):
        self._x = self._x * times

    @x.getter
    def x(self):
        return self._x

a = o(2)
a.x = 5
print a.x
a.printX()
a.printx()
a.printy()
a.printz()

#o.printX() # error
#o.printx() # error
o.printy()
o.printz()

$ ./t.py 
10
10
10
<property object at 0x177a1b0>
x
<property object at 0x177a1b0>
x

Wish this helps.
regards,
Stanley Huang

[Level 1] Login server console with minicom on Ubuntu laptop.

2011-11-02T16:17:00.001+08:00

If you want to use console to login server on Ubuntu laptop,
you could try a useful app named "minicom".
And you could download in by apt-get.
# apt-get -y install minicom

Wish this helps.
regards,
Stanley Huang

[Level 1] Get partition uuid on Ubuntu.

2011-10-27T19:05:00.003+08:00

# blkid
/dev/sda1: UUID="05580ede-bec4-44ae-b1a5-8e8c7b2e7c58" TYPE="ext4" 
/dev/sda2: UUID="3df58602-322b-437a-b65c-de21293f6840" TYPE="swap" 
/dev/sda5: UUID="5417e3d4-4b7d-4a79-a58a-a53cbeab6112" TYPE="ext4"

Wish this helps.
regards,
Stanley Huang

[Level 1] Backup directories by cpio.

2011-10-27T12:47:00.004+08:00

#!/bin/bash
source=$1
target=$2
(cd $source; find . -print | cpio -ocv) | (cd $target; cpio -icuvd)
# cd $source; find . -print | cpio -ocv > /tmp/tmp.cpio
# cd $target; cpio -icuvd < /tmp/tmp.cpio

Wish this helps.
regards,
Stanley Huang

[Level 2] Rock-Paper-Scissors

2011-10-17T14:57:00.005+08:00

#!/bin/env python
import random
RPS = { 'Rock' : 1, 'Paper' : 2, 'Scissors' : 3 } 

if __name__ == '__main__':
    comRPS = random.choice(RPS.keys())
    com = RPS[comRPS]
    youRPS = raw_input('Rock/Paper/Scissors: ')
    you = RPS[youRPS]
    print 'Com: %s' % comRPS
    print 'You: %s' % youRPS
    if com % len(RPS.keys()) ＋ 1 == you:
        print 'You wins!'
    elif you % len(RPS.keys()) ＋ 1 == com:
        print 'Com win!'
    else:
        print 'Break even!'

Wish this helps.
regards,
Stanley Huang

[Level 2] map, reduce and filter methods in Python

2011-10-17T09:49:00.001+08:00

#!/bin/env python
a = [1,2,3,4,5,6,7,8,9]
b = [1,2,3,4]
c = [5,6,7,8]
 
print filter(lambda x: x>5, a)
print map(lambda x,y: x+y, b, c)
print map(lambda x,y: bool(x) and bool(y), a, b)
print map(lambda x,y: bool(x) or bool(y), b, a)
print reduce(lambda x,y: x+y, a)

$ /tmp/t.py 
[6, 7, 8, 9]
[6, 8, 10, 12]
[True, True, True, True, False, False, False, False, False]
[True, True, True, True, True, True, True, True, True]
45

Wish this helps. regards, Stanley Huang

[Level 2] The sample of functools.partial() in Python.

2011-10-16T20:51:00.000+08:00

#!/bin/env python
import functools
try:
print int('10')    ## equiv: print int('10', base=10)
print int('10', 2) ## equiv: print int('10', base=2)
int2 = functools.partial(int, 2) ## no default bonding parameter.
print int2('10')   ## fail, must assign the value of base
int2 = functools.partial(int, base=2) ## set default base to 2
print int2('10')   ## equiv: print int2('10', base=2)
print int2('10', 10) ## fail, TypeError: keyword parameter 'base' was given by position and by name
print int2('10', base=10)

Wish this helps.
regards,
Stanley Huang

[Level 2] How to implement Enum in Python

2011-10-14T14:19:00.002+08:00

#!/bin/env python
class CreateRemove:
    Create = 1
    Remove = 2

class AllowDeny:
    Allow = 1
    Deny = 2

print CreateRemove.Create
print AllowDeny.Deny

Wish this helps.
regards,
Stanley Huang

[Level 1] Restrict user login in ssh.

2011-10-14T14:02:00.000+08:00

You can use AllowUsers/AllowGroups/DenyUsers/DenyGroups in sshd_config to limit user login.
e.g.

# cat /etc/ssh/sshd_config
AllowUsers user1
AllowGroups group1
DenyUsers user2
DenyGroups group2

Wish this helps.
regards,
Stanley Huang

[Level 2] How to execute super() in Python

2011-10-11T11:44:00.000+08:00

If you want to use parent method in Python,
you could use 'super()' method.
But it only support new style class,
therefore, you need to inherit 'object' class when you create your own class.
e.g.

#!/bin/env python
import os, sys 
 
def new_style():
    class NewSystemTools(object):
        def __init__(self):
            pass
        
        def runCmd(self, sCmd):
            return os.popen(sCmd).read()
     
    class MyNewSystemTools(NewSystemTools):
        def __init__(self):
            pass
        
        def runCmd(self, sCmd):
            return super(MyNewSystemTools, self).runCmd(sCmd)

    print '-- new style --' 
    mnst = MyNewSystemTools()
    print mnst.runCmd('ls /')
 
def old_style(): 
    class SystemTools:
        def __init__(self):
            pass
    
        def runCmd(self, sCmd):
            return os.popen(sCmd).read()
    
    class MySystemTools(SystemTools):
        def __init__(self):
            pass
    
        def runCmd(self, sCmd):
            return super(MySystemTools, self).runCmd(sCmd)
    
    print '-- old style --'
    mst = MySystemTools()
    print mst.runCmd('ls /')
 
new_style()
old_style()

# /tmp/t.py 
-- new style --
bin
boot
cdrom
data
dev
etc
filedb
...
-- old style --
Traceback (most recent call last):
  File "/tmp/t.py", line 17, in 
    print mst.runCmd('ls /')
  File "/tmp/t.py", line 14, in runCmd
    return super(MySystemTools, self).runCmd(sCmd)
TypeError: super() argument 1 must be type, not classobj

Wish this helps. regards, Stanley Huang

[ Level 2] How to dump object to file in Python

2011-10-08T03:28:00.001+08:00

#!/bin/env python    
import os, sys 

import marshal
a = [1,2,3]
fd = open('/tmp/t.log', 'wb')
marshal.dump(a, fd) 
fd.close()

fd = open('/tmp/t.log', 'rb')
b = marshal.load(fd)
for i in b:
    print i
fd.close()

Wish this helps.
regards,
Stanley Huang

[Level 2] Vim autocomplete for python.

2011-10-08T01:52:00.000+08:00

If you want to use vim to writing python script.
You could download vim autocomplete for python from here
And then, you could put pythoncomplete.vim in ~/.vim/autoload/
When you writing python, you could use ctrl+p to autocomplete python syntax.

Wish this helps.
regards,
Stanley Huang

[Level 1] Useful vim environment settings.

2011-10-08T01:47:00.001+08:00

My current vim settings are below.
You could get more sample in here.

set nu
set ic
:au BufReadPost * if line("'\"") > 1 && line("'\"") <= line("$") | exe "normal! g'\"" | endif 
set nosmartindent
set tabstop=4
set shiftwidth=4
set expandtab
set hlsearch
syntax on
filetype indent plugin on
set background=dark
set modeline
" filetype indent on
" au FileType python setlocal tabstop=8 expandtab shiftwidth=4 softtabstop=4
" au FileType python setlocal ts=8 et sw=4 sts=4

Wish this helps. regards, Stanley Huang

[Level 1] Encoding in Python script.

2011-10-05T10:02:00.002+08:00

If there are some wording not for ascii, you should add # -*- coding: (encoding) -*-
to prevent compile error.
example error message:
File "./testEncoding.py", line 21
SyntaxError: Non-ASCII character '\xe6' in file ./testForLoop_2.6.py on line 21, but no encoding declared; see http://www.python.org/peps/pep-0263.html for details
e.g.

#!/usr/bin/python2.6
# -*- coding: utf8 -*-

### chinese testing
slang="有嘴說別人無嘴說自己"
sword="嘴"

## error with encoding
#for i in slang:
#  print i

# use python shell to display encoding
# ex.
# >>> "有嘴說別人無嘴說自己"
# '\xe6\x9c\x89\xe5\x98\xb4\xe8\xaa\xaa\xe5\x88\xa5\xe4\xba\xba\xef\xbc\x8c\xe7\x84\xa1\xe5\x98\xb4\xe8\xaa\xaa\xe8\x87\xaa\xe5\xb7\xb1\xe3\x80\x82'
# >>> "嘴"
# '\xe5\x98\xb4'

l=len(sword)
h=0
k=0
count=0
for item in slang:
  k=0
  f=1
  while k<l:
    if slang[h+k]==sword[k]:
      k+=1
      continue
    else:
      f=0
      break
  if f==1:
    count+=1
  h+=1
  if h>=(len(slang)-l):
    break
print count

$ ./testEncoding.py 
2
$

Wish this helps.
regards,
Stanley Huang

[Level 1] Install telnetd in Ubuntu 10.04

2011-10-04T14:15:00.000+08:00

Install telnetd in Ubuntu 10.04:

# sudo su
# apt-get install openbsd-inetd
# echo "telnet stream tcp nowait root /usr/sbin/tcpd /usr/sbin/in.telnetd" >> /etc/inetd.conf
# /etc/init.d/openbsd-inetd restart

Wish this helps.
regards,
Stanley Huang

[Level 3] More decorator samples.

2011-09-29T12:40:00.013+08:00

There is a good website to demo decorator sample codes. here

'''
Usage:
@deprecated
def func(): ...
'''
class deprecated:
    def __init__(self, func):
        self.func = func
        self.__name__ = self.func.__name__
        self.__doc__ = self.func.__doc__
        self.__dict__.update(self.func.__dict__)
        pass
    
    """This is a decorator which can be used to mark functions
    as deprecated. It will result in a warning being emitted
    when the function is used."""
    def __call__(self, *args, **kwargs):
        msg = "Call to deprecated function %s()." % self.func.__name__
        ## Suppressing Warnings
        warnings.simplefilter("ignore")
        warnings.warn(msg, category=DeprecationWarning)
        ## Delete ignore filter 
        del warnings.filters[0]
        (tmpDebug2Console, libcommon.bDebug2Console) = (libcommon.bDebug2Console, False)
        libcommon.debug(msg, logfile='deprecated.log')
        libcommon.bDebug2Console = tmpDebug2Console
        return self.func(*args, **kwargs)

'''
Usage:
#{case 1}
libdecorator.func_state_flag_default = True/False/None
libdecorator.func_state_flag_test = True/False/None
libdecorator.func_state_flag = True/False/None
@funcState()
@funcState(func_set='test')
@funcState(func_set='test', skip_return_value=True)
def func(): ...

#{case 2}
@funcEnabled
def func(): ...

#{case 3}
@funcDisabled
def func(): ...

#{case 4}
@funcSkipped(True/False/...)
def func(): ...
'''
#global func_state_flag
#func_state_flag=None
class funcState:
    def __init__(self, func_set='default', skip_return_value=True):
        self.func_set = func_set
        self.skip_return_value = skip_return_value
        
    def __call__(self, func):            
        if 'func_state_flag_%s' % self.func_set in locals():
            self.func_state_flag = locals()['func_state_flag_%s' % self.func_set]
        elif 'func_state_flag_%s' % self.func_set in globals():
            self.func_state_flag = globals()['func_state_flag_%s' % self.func_set]
        elif 'func_state_flag' in locals():
            self.func_state_flag = locals()['func_state_flag']
        elif 'func_state_flag' in globals():
            self.func_state_flag = globals()['func_state_flag']
        else:
            self.func_state_flag = True
        
        self.func_enabled = funcEnabled()
        self.func_disabled = funcDisabled(func.__name__)
        self.func_skipped = funcSkipped(self.skip_return_value)

        funcState = {True: self.func_enabled,
                     False: self.func_disabled,
                     None: self.func_skipped
                     }[self.func_state_flag]

        @funcState
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            return func(*args, **kwargs)
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

class funcEnabled:
    def __init__(self):
        pass
    
    "This decorator enables the provided function, and does nothing."
    def __call__(self, func):
        return func

class funcDisabled:
    def __init__(self, func_name=None):
        self.func_name = func_name
        pass
    
    "This decorator disables the provided function, and does nothing."
    def __call__(self, func):
        func_name = self.func_name if self.func_name else func.__name__
        assert False, 'function %s() is disabled!' % func_name

class funcSkipped:
    def __init__(self, return_value=True):
        self.return_value = return_value
        pass
    
    "This decorator skip the provided function, and return the specific value."
    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            return self.return_value
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
#{case 1}
libdecorator.bIgnoreYN = True/False
self.bIgnoreYN = True/False
@yn('Are these correct?', continue_flag='yes', exit_program=True, return_type=(yn/tf/value), continue_message='continue message!', exit_message='exit message!')
def test():
  print 'test()'
  ## return True/'yes' for @libdecorator.yn()
  return True/'yes'

#{case 2}
class ctest:
  def __init__(self):
    self.bIgnoreYN = False
    pass

  @yn('Are these correct?', continue_flag='yes', exit_program=True, return_type=(yn/tf/value), continue_message='continue message!', exit_message='exit message!')
  def test(self):
    print 'ctest.test()'
    ## return True/'yes' for @libdecorator.yn()
    return True/'yes'
'''
class yn:
    def __init__(self, prompt, continue_flag='yes', exit_program=True, return_type='value', continue_message=None, exit_message=None):
        self.prompt = prompt
        self.continue_flag = continue_flag
        self.exit_program = exit_program
        self.return_type = return_type
        self.continue_message = continue_message
        self.exit_message = exit_message
        
    def __call__(self, func):
        yes = 'YES' if self.continue_flag.lower() == 'yes' else 'yes'
        no = 'NO' if self.continue_flag.lower() == 'no' else 'no'
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            sReturn = func(*args, **kwargs)

            ## check attribute of instance
            if args and isinstance(args[0], object) and hasattr(args[0], 'bIgnoreYN'):
                o = args[0]
                bIgnoreYN = o.bIgnoreYN
            else:
                ## check global/local variable
                if 'bIgnoreYN' in locals():
                    bIgnoreYN = locals()['bIgnoreYN']
                elif 'bIgnoreYN' in globals():
                    bIgnoreYN = globals()['bIgnoreYN']
                else:
                    bIgnoreYN = False
            ## filter ignore flag and set default to False
            if bIgnoreYN not in (True, False):
                bIgnoreYN = False
            
            while not bIgnoreYN:
                bh = libinterrupt.BreakHandler(-1)
                bh.enable()
                try:
                    yn = raw_input('%s [%s/%s]: ' % (self.prompt, yes, no))
                except EOFError:
                    yn = None
                bh.disable()
                    
                if yn.lower() in ('yes', 'no'):
                    if yn.lower() == self.continue_flag.lower():
                        if self.continue_message: print self.continue_message
                    else:
                        if self.exit_message: print self.exit_message
                        if self.exit_program: exit(1)
                    break
                else:
                    print 'Only accept %s/%s!' % (yes, no)
            
            if bIgnoreYN:
                return sReturn
            else:
                if self.return_type.lower() == 'yn':
                    sReturn = yn.lower()
                elif self.return_type.lower() == 'tf':
                    sReturn = True if yn.lower() == self.continue_flag else False
                elif self.return_type.lower() == 'value':
                    pass
                else:
                    assert False, 'Error return type(%s)!' % self.return_type
                return sReturn
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f


'''
Usage:
@checkUnixPassword()
@checkUnixPassword('success_message', 'fail_message', 'admin')
def test(): ...
'''
class checkUnixPassword:
    def __init__(self, success_message=None, fail_message=None, user=None):
        self.success_message = success_message
        self.fail_message = fail_message
        self.user = user if user else libresourcemanager.getAccountName()

    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            passwd = getpass.getpass("Please enter %s's password: " % self.user).rstrip()
        
            ## get password field from shadow file
            sp = libcommon.shadowPassword()
            sp.setPasswordField(self.user)
        
            ## get password from crypt algorithm
            up = libcommon.unixPasswordEncryptor(passwd, sp.getPasswordEncryptType(), sp.getPasswordSalt())        
            if not sp.getPasswordField(self.user) == up.getPasswordField():
                if self.fail_message: print self.fail_message
                exit(1)
            else:
                if self.success_message: print self.success_message
                return func(*args, **kwargs)
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
@checkCLIPassword()
@checkCLIPassword('success_message', 'fail_message')
def test(): ...
'''
class checkCLIPassword:
    def __init__(self, success_message=None, fail_message=None):
        self.success_message = success_message
        self.fail_message = fail_message
        self.ENABLE_PASSWORD_FLAG_FILE = '/opt/ruckuswireless/wsg/conf/enable_password.done'

    def getEncyptCLIPasswd(self):
        cmd = 'head -1 %s' % self.ENABLE_PASSWORD_FLAG_FILE
        return os.popen(cmd).read().strip()

    def encryptPasswd(self, plain_text):
        return  hashlib.sha1(plain_text).hexdigest()

    def isValidCLIPasswd(self, plain_text):
        return self.getEncyptCLIPasswd() == self.encryptPasswd(plain_text)

    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            passwd = getpass.getpass("Please enter cli password: ").rstrip()
            
            if not self.isValidCLIPasswd(passwd):
                if self.fail_message: print self.fail_message
                exit(1)
            else:
                if self.success_message: print self.success_message
                return func(*args, **kwargs)
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
libdecorator.pek2c_enable_flag = True/False
Usage:
@pek2c() 
@pek2c(before_after='before/after', message='message') 
@pek2c(before_after='before/after', message='message', confirm_message='confirm_message') 
def test(): ...
'''
class pek2c:
    def __init__(self, before_after='after', message='continue', confirm_message=''):
        self.before_after = before_after
        self.confirm_message = confirm_message
        key = 'Enter "%s"' % self.confirm_message if self.confirm_message else 'Press Enter key'
        self.message = '%s to %s... ' % (key, message)
    
    def runPek2c(self, message=''):
        if 'pek2c_enable_flag' in locals():
            pek2c_enable_flag = locals()['pek2c_enable_flag']
        elif 'pek2c_enable_flag' in globals():
            pek2c_enable_flag = globals()['pek2c_enable_flag']
        else:
            pek2c_enable_flag = True
        
        if pek2c_enable_flag:
            while True:
                bh = libinterrupt.BreakHandler(-1)
                bh.enable()
                try:
                    x = raw_input(message)
                except EOFError:
                    x = None
                bh.disable()
                
                if self.confirm_message and x != self.confirm_message:
                    continue
                else:
                    break
    
    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            if self.before_after == 'before': self.runPek2c(self.message)
            sReturn = func(*args, **kwargs)
            if self.before_after == 'after' : self.runPek2c(self.message)
            return sReturn
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
@dumpArgs  
def test(a, b, c): ...
'''
class dumpArgs:
    def __init__(self, func):
        self.func = func
        "This decorator dumps out the arguments passed to a function before calling it"
        self.argnames = func.func_code.co_varnames[:func.func_code.co_argcount]
        self.fname = func.func_name
        self.__name__ = func.__name__
        self.__doc__ = func.__doc__
        self.__dict__.update(func.__dict__)
        
    def __call__(self, *args, **kwargs):
        print self.fname, ":", ', '.join(
            '%s=%r' % entry
            for entry in zip(self.argnames,args) + kwargs.items())
        return self.func(*args, **kwargs)

'''
Usage:
@retry()
@retry(libdecorator.RetryException, nRetry=3, nDelay=3, nBackOff=2)
def test(a, b, c):
    raise retryException('retry')
'''
class RetryException(Exception):
    def __init__(self, message=''):
        self.__message = message
    
    def __str__(self):
        return self.__message
    pass

class retry:
    def __init__(self, ExceptionToCheck=RetryException, nRetry=3, nDelay=1, nBackOff=1):
        self.ExceptionToCheck = ExceptionToCheck
        self.nRetry = nRetry
        self.nDelay = nDelay
        self.nBackOff = nBackOff
    
    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kwargs):
            for i in range(1, self.nRetry+1):
                try:
                    sReturn = func(*args, **kwargs)
                    return sReturn
                except self.ExceptionToCheck, e:
                    if i < self.nRetry:
                        print '%s %s time but fail, wait %s sec to continue' % ('Run' if i == 1 else 'Retry', i, self.nDelay)
                        time.sleep(self.nDelay)
                        self.nDelay = self.nDelay * self.nBackOff
                    if i == self.nRetry:
                        raise e
                except Exception, e:
                    raise e
                else:
                    break
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
from threading import Lock
my_lock = Lock()
@synchronized(my_lock)
def test(a, b, c): ...
'''
class synchronized:
    def __init__(self, lock):
        self.lock = lock
    
    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kw):
            self.lock.acquire()
            try:
                return func(*args, **kw)
            finally:
                self.lock.release()
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
@fileLockSynchronized('my_lock1')
@fileLockSynchronized('my_lock2', 10, 1)
def test(a, b, c): ...
'''
class fileLockSynchronized:
    def __init__(self, lockfile, timeout=10, delay=1):
        self.lockfile = lockfile
        self.timeout = timeout
        self.delay = delay
    
    def __call__(self, func):
        @functools.wraps(func)
        def wrapped_f(*args, **kw):
            FileLock = libresourcemanager.FileLock(self.lockfile, self.timeout, self.delay)
            try:
                FileLock.acquire()
                return func(*args, **kw)
            finally:
                FileLock.release()
#        wrapped_f.__name__ = func.__name__
#        wrapped_f.__doc__ = func.__doc__
#        wrapped_f.__dict__.update(func.__dict__)
        return wrapped_f

'''
Usage:
@saveCurrentWorkingDirectory
def func(): ...
'''
class saveCurrentWorkingDirectory:
    def __init__(self, func):
        self.func = func
        self.__name__ = self.func.__name__
        self.__doc__ = self.func.__doc__
        self.__dict__.update(self.func.__dict__)
        pass
    
    def __call__(self, *args, **kwargs):
        cwd = os.getcwd()
        sReturn = self.func(*args, **kwargs)
        os.chdir(cwd)
        return sReturn

'''
Usage:
@singleton
class cls: ...
'''

'''
def singleton(cls):
    instances = {}
    def getInstance(*args, **kwargs):
        if cls not in instances:
            instances[cls] = cls(*args, **kwargs)
        return instances[cls]
    return getInstance
'''
class singleton:
    def __init__(self, cls):
        self.instances = {}
        self.cls = cls
        pass
    
    def __call__(self, *args, **kwargs):
        if self.cls not in self.instances:
            self.instances[self.cls] = self.cls(*args, **kwargs)
        return self.instances[self.cls]

Wish this helps.
regards,
Stanley Huang

[Level 1] Compile python scripts.

2011-09-29T00:43:00.000+08:00

If you want to compile python script manually, you could use following command.

$ python -c "import compileall; compileall.compile_dir('./', force=1)"

Wish this helps.
regards,
Stanley Huang

[Level 2] enumrate() in Python.

2011-09-28T23:25:00.000+08:00

#!/bin/env python
aWeek = ['Sun', 'Mon','Tue','Wed', 'Thu', 'Fri', 'Sat']

## without enumerate()
print 'without enumerate()...'
index = 0
for day in aWeek:
    print index, day
    index += 1

## without enumerate()
print 'without enumerate()...'
for index in range(len(aWeek)):
    print index, aWeek[index]

## with enumerate()
print 'with enumerate()...'
for (index, day) in enumerate(aWeek):
    print index, day

without enumerate()...
0 Sun
1 Mon
2 Tue
3 Wed
4 Thu
5 Fri
6 Sat
without enumerate()...
0 Sun
1 Mon
2 Tue
3 Wed
4 Thu
5 Fri
6 Sat
with enumerate()...
0 Sun
1 Mon
2 Tue
3 Wed
4 Thu
5 Fri
6 Sat

Use enumerate() would make code easier to read.

Wish this helps.
regards,
Stanley Huang

[Level 2] Python Decorator Sample.

2011-09-26T22:51:00.002+08:00

Another sample for Python decorator.

#!/usr/bin/env python
class ServiceFee:
  def __init__(self, f):
    self.f = f
    pass
  def __call__(self, *args, **kargs):
    return self.f(*args, **kargs)*1.1

class TaxFee:
  def __init__(self, f):
    self.f = f
    pass
  def __call__(self, *args, **kargs):
    return self.f(*args, **kargs)*1.05

@ServiceFee
@TaxFee
def beef(pricePerKG, KG):
  return pricePerKG*KG

print 'Beef $10/KG, and buy 5KG=%s' % beef(10, 5)
print 'Beef $20/KG, and buy 10KG=%s' % beef(20, 10)

$ ./t.py
Beef $10/KG, and buy 5KG=57.75
Beef $20/KG, and buy 10KG=231.0

Wish this helps. regards, Stanley Huang

[Level 2] Join lines by sed commands.

2011-09-26T16:12:00.000+08:00

If you want to join lines in shell script, you could use sed command.

#!/bin/bash
cat > /tmp/t.txt <<EOF
eth0
eth1
eth2
eth3
eth4
eth5
eth6
lo0
EOF
sed -e :a -e '$!N; s/\n/,/; ta' /tmp/t.txt

$ ./joinLinesBySed.sh
eth0,eth1,eth2,eth3,eth4,eth5,eth6,lo0

Wish this helps.
regards,
Stanley Huang

[Level 2] How to implement decorator in Python (II).

2011-09-26T15:18:00.003+08:00

Months ago, I wrote a simple decorator, without decorator parameters, implement in Python.
[Level 2] How to implement decorator in Python (I).
Now, I'll show you how to implement decorator with parameter.

The sample of Python decorator (1, by class):

#!/bin/env python

class Decorator:
    def __init__(self, man, woman):
        self.man = man 
        self.woman = woman
        pass
    def __call__(self, f): 
        def wrapped_f(*args, **kargs):
            print 'hello %s' % self.man
            f(*args, **kargs)
            print 'world %s' % self.woman
        return wrapped_f

@Decorator('stanley', 'christy')
def test(who):
    print 'test(%s)' % who 
   
test('joseph')

$ ./test.py
hello stanley
test(joseph)
world christy

The sample of Python decorator (2, by wrap function):

#!/bin/env python
def Decorator(man, woman):
    def wrap(f):
        def wrapped_f(*args, **kargs):
            print 'hello %s' % man 
            f(*args, **kargs)
            print 'world %s' % woman
        return wrapped_f
    return wrap

@Decorator('stanley', 'christy')
def test(who):
    print 'test(%s)' % who 

test('joseph')

$ ./test.py
hello stanley
test(joseph)
world christy

Wish this helps.
regards,
Stanley Huang

[Level 2] Testing combination of loop and try.

2011-09-21T14:58:00.000+08:00

Sample code and test result are as the following:

#!/bin/env python
class e(Exception):
    def __init__(self):
       pass

server_list = [1,2,3,4,5]
for server in server_list:
    try:
        sReturn = server
        print 's:%s' % sReturn
        if sReturn<3: continue
        if sReturn==3: raise e
        print 'try:%s' % sReturn
    except Exception, e:
        print 'except1:%s' % sReturn
        continue
        print 'except2:%s' % sReturn
    else:
        print 'else:%s' % sReturn
        break
    finally:
        print 'finally:%s' % sReturn

$ ./t.py 
s:1
finally:1
s:2
finally:2
s:3
except1:3
finally:3
s:4
try:4
else:4
finally:4

Wish this helps.
regards,
Stanley Huang

[Level 1] Make Nautilus always use Location Entry in Ubuntu 10.04

2011-08-31T13:51:00.000+08:00

Hi all:

FYI.
http://ubuntuguide.net/make-nautilus-always-use-location-entry-in-ubuntu-10-04

Wish this helps.
regards,
Stanley Huang

[Level 1] Install autoexpect

2011-08-31T09:51:00.000+08:00

$ autoexpect -f ./auto.exp

autoexpect started, file is ./auto.exp

$ ssh stanley@localhost

The authenticity of host 'localhost (127.0.0.1)' can't be established.

RSA key fingerprint is 36:12:a4:49:26:fe:18:0d:77:bf:c7:e8:36:86:b7:f2.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'localhost' (RSA) to the list of known hosts.

stanley@localhost's password: 

Welcome to Ubuntu 11.04 (GNU/Linux 2.6.38-10-generic x86_64)



 * Documentation:  https://help.ubuntu.com/



*** /dev/sda5 will be checked for errors at next reboot ***



Last login: Thu Jul 28 12:17:50 2011 from stanley-ubuntu

$ ls

apache-cassandra-0.8.1-SNAPSHOT.jar  Public

auto.exp                             Ruckus

Desktop                              script.exp

Documents                            scripts

Downloads                            scripts_20110302.tgz

eclipse                              Self_assesment_201103.pdf

English_Survey.pdf                   SUR-1-Vesu_Piplod_NewCityLigh_1.png

examples.desktop                     Templates

minicom.log                          Videos

Music                                vmEsxi4.1_customize.docx

Perforce                             workspace

Pictures

$ pwd

/home/stanley

$ exit

logout

Connection to localhost closed.

$ exit

exit

autoexpect done, file is ./auto.exp

$ ls -al ./auto.exp

-rwxr-xr-x 1 stanley stanley 4004 2011-08-31 09:44 ./auto.exp

$ cat ./auto.exp

#!/usr/bin/expect -f

#

# This Expect script was generated by autoexpect on Wed Aug 31 09:44:02 2011

# Expect and autoexpect were both written by Don Libes, NIST.

#

# Note that autoexpect does not guarantee a working script.  It

# necessarily has to guess about certain things.  Two reasons a script

# might fail are:

#

# 1) timing - A surprising number of programs (rn, ksh, zsh, telnet,

# etc.) and devices discard or ignore keystrokes that arrive "too

# quickly" after prompts.  If you find your new script hanging up at

# one spot, try adding a short sleep just before the previous send.

# Setting "force_conservative" to 1 (see below) makes Expect do this

# automatically - pausing briefly before sending each character.  This

# pacifies every program I know of.  The -c flag makes the script do

# this in the first place.  The -C flag allows you to define a

# character to toggle this mode off and on.



set force_conservative 0  ;# set to 1 to force conservative mode even if

			  ;# script wasn't run conservatively originally

if {$force_conservative} {

	set send_slow {1 .1}

	proc send {ignore arg} {

		sleep .1

		exp_send -s -- $arg

	}

}



#

# 2) differing output - Some programs produce different output each time

# they run.  The "date" command is an obvious example.  Another is

# ftp, if it produces throughput statistics at the end of a file

# transfer.  If this causes a problem, delete these patterns or replace

# them with wildcards.  An alternative is to use the -p flag (for

# "prompt") which makes Expect only look for the last line of output

# (i.e., the prompt).  The -P flag allows you to define a character to

# toggle this mode off and on.

#

# Read the man page for more info.

#

# -Don





set timeout -1

spawn $env(SHELL)

match_max 100000

expect -exact "]0;$\$ "

send -- "ssh stanely@"

expect -exact [K"

send -- ""

expect -exact [K"

send -- ""

expect -exact [K"

send -- ""

expect -exact [K"

send -- "ley@localhost\r"

expect -exact "ley@localhost\r

The authenticity of host 'localhost (127.0.0.1)' can't be established.\r

RSA key fingerprint is 36:12:a4:49:26:fe:18:0d:77:bf:c7:e8:36:86:b7:f2.\r

Are you sure you want to continue connecting (yes/no)? "

send -- "yes\r"

expect -exact "yes\r

Warning: Permanently added 'localhost' (RSA) to the list of known hosts.\r\r

stanley@localhost's password: "

send -- "MyPassword\r"

expect -exact "\r

Welcome to Ubuntu 11.04 (GNU/Linux 2.6.38-10-generic x86_64)\r

\r

 * Documentation:  https://help.ubuntu.com/\r

\r

*** /dev/sda5 will be checked for errors at next reboot ***\r

\r

Last login: Thu Jul 28 12:17:50 2011 from stanley-ubuntu\r\r

]0;$\$ "

send -- "ls\r"

expect -exact "ls\r

[0m[01;31mapache-cassandra-0.8.1-SNAPSHOT.jar[0m  [01;34mPublic[0m\r

[01;32mauto.exp[0m                             [01;34mRuckus[0m\r

[01;34mDesktop[0m                              [01;32mscript.exp[0m\r

[01;34mDocuments[0m                            [01;34mscripts[0m\r

[01;34mDownloads[0m                            [01;31mscripts_20110302.tgz[0m\r

[01;34meclipse[0m                              Self_assesment_201103.pdf\r

English_Survey.pdf                   [01;35mSUR-1-Vesu_Piplod_NewCityLigh_1.png[0m\r

examples.desktop                     [01;34mTemplates[0m\r

minicom.log                          [01;34mVideos[0m\r

[01;34mMusic[0m                                vmEsxi4.1_customize.docx\r

[01;34mPerforce[0m                             [01;34mworkspace[0m\r

[01;34mPictures[0m\r

]0;$\$ "

send -- "pwd\r"

expect -exact "pwd\r

/home/stanley\r

]0;$\$ "

send -- "exit\r"

expect -exact "exit\r

logout\r

Connection to localhost closed.\r\r

]0;$\$ "

send -- "exit\r"

expect eof

$

Wish this helps. regards, Stanley Huang

[Level 2] How to simulate udevinfo in Ubuntu 11.04

2011-08-30T09:49:00.000+08:00

When you try to use command udevinfo in Ubuntu 11.04, you would get the response "udevinfo: command not found".
How to simulate the command udevinfo?
Use command udevinfo with info option.
Ex.
# udevadm info -q all -n /dev/video0
You could also create a function named udevinfo as you like
Ex.

# udevinfo () { udevadm info -a -p `udevadm info -q path -n "$1"`; }

# udevinfo /dev/video0

...

Wish this helps.
regards,
Stanley Huang

[Level 2] python oui lookup

2011-08-28T08:30:00.000+08:00

$ sudo apt-get -y install python-netaddr

...

$ cd /usr/share/pyshared/netaddr/eui

$ sudo python ./ieee.py 

downloading latest copy of http://standards.ieee.org/regauth/oui/oui.txt

downloading latest copy of http://standards.ieee.org/regauth/oui/iab.txt

$ python

>>> from netaddr import *

>>> mac = EUI('bc:ae:c5:3b:fc:5e')

>>> print mac.oui.registration().org

ASUSTek COMPUTER INC.

Wish this helps.
regards,
Stanley Huang

[Level 2] Use expect to run openvpn.

2011-08-27T11:01:00.002+08:00

#!/usr/bin/expect

set password [lindex $argv 0]

spawn sudo openvpn --script-security 2 --config /home/stanley/Ruckus/RuckusVPN_config/ruckus_taipei.ovpn

expect Enter\ Private\ Key\ Password:

sleep 1

send $password

sleep 1

interact

Test run:

# ./openvpn.expect mypassword

Wish this helps. regards, Stanley Huang

[Level 2] Pexpect of Python.

2011-08-26T11:46:00.000+08:00

#!/bin/env python

import os, sys

import StringIO





class AuthorizedKeyError(Exception):

    def __init__(self, host):

        self.value = 'Did not be authorized by remote server(%s)!!!' % host

    def __str__(self):

        return repr(self.value)



class FileSyncManager:

    def fileSync(self, user, host, file):

        output = StringIO.StringIO()

        sCmd = 'scp %s %s@%s:%s' % (file, user, host, file)

        self.debug(sCmd)

        

        scp = pexpect.spawn(sCmd)

        scp.logfile = output

        

        while True:

            index = scp.expect(["Are you sure you want to continue connecting (yes/no)?", "password:" ,pexpect.TIMEOUT, pexpect.EOF]) 

            if index == 0: 

                scp.sendline('yes')

            elif index == 1:

                raise AuthorizedKeyError(host)

            else:

                break

            

        if scp.exitstatus:

            error_message = output.getvalue().rstrip()

            self.debug(error_message)

            print error_message

            

        scp.close(True)

Wish this helps. regards, Stanley Huang

[Level 2] How to skip StrictHostKeyChecking in ssh.

2011-08-09T15:39:00.003+08:00

If you want to ignore strict host key checking, even skip saving the host key info,
you could use option UserKnownHostsFile, StrictHostKeyChecking.
Ex.

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no stanley@192.168.1.1

or modify /etc/ssh/ssh_config for global settings.

# cat /etc/ssh/ssh_config
    ...
    UserKnownHostsFile /dev/null
    StrictHostKeyChecking no
    ...

Wish this helps.
regards,
Stanley Huang

[Level 2] Recursive directory list

2011-08-04T15:01:00.000+08:00

#!/bin/env python
import os, sys
wholeFileList = []
baseDir = sys.argv[1]
for parentDir, childDirList, fileList in os.walk(baseDir):
    for file in fileList:
        wholeFileList.append(os.path.join(parentDir,file))
for file in wholeFileList:
    print file

# ./t.py /tmp/d
/tmp/d/1.txt
/tmp/d/2/6.txt
/tmp/d/2/3/a.txt
/tmp/d/2/4/b.txt
/tmp/d/2/5/c.txt

Wish this helps. regards, Stanley Huang

[Level 3] Python performance.

2011-07-28T11:29:00.000+08:00

Days ago, I discussed the coding style of python,
and I use one example to present what I want you to know.

The most 2 ways to use of renaming a file are:
1. Use rename method os moudle [ os.rename(f1, f2) ],
2. Use external command by popen method[ os.popen('mv f1 f2') ].

Some people might think there are not much different between these two ways.
But after a stress test, you will realize it did has different between these two ways.

The sample code and test run are as the following.

#!/bin/env python
import os, sys

from time import gmtime, strftime

n = 10000

f1 = '/1'
f2 = '/2'

os.popen('touch %s' % f1).read()
print strftime("%a, %d %b %Y %H:%M:%S +0000", gmtime())
for i in range(n):
    if os.path.exists(f1):
        os.rename(f1, f2)
    else:
        os.rename(f2, f1)
print strftime("%a, %d %b %Y %H:%M:%S +0000", gmtime())
for i in range(n):
    if os.path.exists(f1):
        os.popen('mv %s %s' % (f1, f2)).read()
    else:
        os.popen('mv %s %s' % (f2, f1)).read()
print strftime("%a, %d %b %Y %H:%M:%S +0000", gmtime())

# ./t.py 
Thu, 28 Jul 2011 03:18:55 +0000
Thu, 28 Jul 2011 03:18:56 +0000
Thu, 28 Jul 2011 03:19:38 +0000
/# ./t.py 
Thu, 28 Jul 2011 03:19:55 +0000
Thu, 28 Jul 2011 03:19:55 +0000
Thu, 28 Jul 2011 03:20:38 +0000

To many system call will have impact the performance,
and also the portable capability.

Wish this helps.
regards,
Stanley Huang

[Level 3] Python performance tips.

2011-07-28T11:11:00.003+08:00

Python performance tips.

http://wiki.python.org/moin/PythonSpeed

http://wiki.python.org/moin/PythonSpeed/PerformanceTips

Wish this helps.
regards,
Stanley Huang

[Level 2] How to get python version on run-time.

2011-07-27T10:45:00.000+08:00

#!/bin/env python
import sys
print sys.version

# python -c 'import sys; print sys.version'

2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3]

Wish this helps. regards, Stanley Huang

[Level 2] How to allow using symbolic link in tomcat? (with python code)

2011-07-12T19:44:00.000+08:00

If you want to allow using symbolic link in tomcat,
you could add and attribute called "context" with an key "allowLinking"
eg.
<Context path="/myapp" allowLinking="true"/>
and I wrote a sample python code to do this.

#!/bin/env python
import os,sys
from xml.dom import minidom
def getXMLDom(FILE):
    return minidom.parse(open(FILE,'r'))

def saveXML(FILE, dom):
    fd = open(FILE, 'w')
    fd.write(dom.toxml())
    fd.close()

def appendElement(FILE, base_element_name, element_name, attr_key_val, element_index=0, backup_flag='y'):
    if backup_flag == 'y': backupConfigFile(FILE)
    dom = getXMLDom(FILE)
    e = dom.createElement(element_name)
    for attr, val in attr_key_val:
        e.setAttribute(attr, val)
    dom.getElementsByTagName(base_element_name)[element_index].appendChild(e)
    saveXML(FILE, dom)

xmlFile = prefix+'/opt/ruckuswireless/3rdparty/tomcat/conf/server.xml'
base_element_name = 'Host'
element_name = 'Context'
attr_key_val = [ 
  ["path", "/myapp"],
  ["allowLinking", "true"]
]
appendElement(xmlFile, base_element_name, element_name, attr_key_val)

Wish this helps. regards, Stanley Huang

[Level 1] How to install Jython on Ubuntu.

2011-06-29T10:41:00.001+08:00

You have two way to install jython on Ubuntu:
1. Use apt-get:

# sudo apt-get -y install jython
# jython --version

2. Download source:

# cd /tmp
# wget http://sourceforge.net/projects/jython/files/jython/2.5.2/jython_installer-2.5.2.jar/download
# mv ./download ./jython.jar
# java -jar ./jython.jar
... following the wizard.
# export PATH=$PATH:/opt/jython/bin # (if you installed Jython in /opt/jython)
# jython --version

Wish this helps.
regards,
Stanley Huang

[Level 1] How to install Scala on Ubuntu.

2011-06-29T09:50:00.000+08:00

You have two way to install it.
1. Use apt-get:

# sudo apt-get -y install scala

2. Download source: (browse http://www.scala-lang.org/downloads first)

# cd /opt
# wget http://www.scala-lang.org/downloads/distrib/files/scala-2.9.0.1.tgz
# tar -C /opt zxf ./scala-2.9.0.1.tgz
# export PATH=$PATH:/opt/scala-2.9.0.1/bin
# scala -version

Wish this helps.
regards,
Stanley Huang

[Level 2] Enable wireless of network-manager in Ubuntu 11.04

2011-06-28T19:52:00.002+08:00

Once you upgrade Ubuntu from 10.04 to 11.04, and you will find out the network-manager won't manage wireless any more. But you still could change NetworkManager.conf to enable it.

# sudo vi /etc/NetworkManager/NetworkManager.conf
and change "managed" attribute from "false" to “true“ and restart it.
# sudo /etc/init.d/network-manager restart

Wish this helps.
regards,
Stanley Huang

[Level 1] How to set ntp client in Ubuntu.

2011-06-22T11:27:00.000+08:00

If you want to sync time with time server in Ubuntu,
you could use command ntpdate + time_server.
And don't forget write date into bios too.

ntpdate time.stdtime.gov.tw && hwclock -w

Wish this helps.
regards,
Stanley Huang

[Level 1] How to grep in file.

2011-06-15T19:31:00.000+08:00

Just use -P and '\t' with grep command.
Ex.

# grep -P '\t' /etc/passwd

Wish this helps. regards, Stanley Huang

[Level 1] MP3 editor in Ubuntu.

2011-06-01T15:41:00.002+08:00

If you want to modify mp3 in Ubuntu,
you could use "audacity".
And you could download it from reposity.
The commands as the following.

# apt-cache search audacity
create-resources - shared resources for use by creative applications
audacity - fast, cross-platform audio editor
audacity-data - fast, cross-platform audio editor (data)
audacity-dbg - fast, cross-platform audio editor (debug)
vamp-plugin-sdk - audio analysis and feature extraction plugins (SDK)
# apt-get -y install audacity

After for a while, I heard a good tool to convert mp4 to mp3 call avidemux

# apt-cache search avidemux
libavidemux0 - a free video editor - shared libraries
avidemux - a free video editor - GTK version
avidemux-cli - a free video editor - command line version
avidemux-common - a free video editor - Internationalization files
avidemux-plugins-cli - a free video editor - CLI plugins
avidemux-plugins-common - a free video editor - common files for plugins
avidemux-plugins-gtk - a free video editor - GTK plugins
avidemux-plugins-qt - a free video editor - Qt plugins
avidemux-qt - a free video editor - QT version
# apt-get -y install avidemux

Wish this helps. regards, Stanley Huang

[Level 1] Linux simulator on web.

2011-05-25T02:31:00.000+08:00

Petty cool stuff.
Linux simulator on web.
Please refer to the foll
http://bellard.org/jslinux

Wish this helps.
regards,
Stanley Huang

[Level 2] How to check the resource limitation of specific process in Ubuntu.

2011-05-20T20:35:00.000+08:00

How to check the resource limitation of specific process in Ubuntu.
You can follow the below steps:

# ps -ef | grep nfsd4
root      1098     2  0 May13 ?        00:00:00 [nfsd4]
root     18674 18607  0 20:32 pts/15   00:00:00 grep --color=auto nfsd4
# cat /proc/1098/limits 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            10485760             unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             47490                47490                processes 
Max open files            1024                 1024                 files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       47490                47490                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
#

Wish this helps.
regards,
Stanley Huang

[Level 2] How to connect wireless ap by command in Ubuntu.

2011-05-10T11:41:00.000+08:00

How to connect wireless ap by command in Ubuntu?
You can use two commands, 'iwlist', 'iwconfig'.

# iwlist --help
Usage: iwlist [interface] scanning [essid NNN] [last]
              [interface] frequency 
              [interface] channel 
              [interface] bitrate 
              [interface] rate 
              [interface] encryption 
              [interface] keys 
              [interface] power 
              [interface] txpower 
              [interface] retry 
              [interface] ap 
              [interface] accesspoints 
              [interface] peers 
              [interface] event 
              [interface] auth 
              [interface] wpakeys 
              [interface] genie 
              [interface] modulation 
# iwlist wlan0 scanning essid ap_name
# iwconfig --help
Usage: iwconfig [interface]
                interface essid {NNN|any|on|off}
                interface mode {managed|ad-hoc|master|...}
                interface freq N.NNN[k|M|G]
                interface channel N
                interface bit {N[k|M|G]|auto|fixed}
                interface rate {N[k|M|G]|auto|fixed}
                interface enc {NNNN-NNNN|off}
                interface key {NNNN-NNNN|off}
                interface power {period N|timeout N|saving N|off}
                interface nickname NNN
                interface nwid {NN|on|off}
                interface ap {N|off|auto}
                interface txpower {NmW|NdBm|off|auto}
                interface sens N
                interface retry {limit N|lifetime N}
                interface rts {N|auto|fixed|off}
                interface frag {N|auto|fixed|off}
                interface modulation {11g|11a|CCK|OFDMg|...}
                interface commit 
       Check man pages for more details.
# iwconfig wlan0 essid ap_name
# iwconfig wlan0 enc passphrase
# dhclient wlan0

Wish this helps. regards, Stanley Huang

[Level 1] How to get user home directoy in Python

2011-04-27T14:05:00.000+08:00

#!/bin/env python
import os
print os.path.expanduser('~')
print os.path.expanduser('~stanley')
print os.environ['HOME']

./getUserHomeDirectory.py 
/home/stanley
/home/stanley
/home/stanley

Wish this helps.
regards,
Stanley Huang

[Level 1] How to setup Cassandra home directory

2011-04-26T19:14:00.000+08:00

If you want to create multi Cassandra instances and only use one copy of binary.
You need to setup CASSANDRA_HOME environment variable.
If you only use symbolic link to link binary file,
Cassandra will search the real binary directory and set its parent directory as the home,
if you don't setup the CASSANDRA_HOME environment variable.

# export CASSANDRA_HOME=[new directory you want]
# cd [new directory you want]
# ./bin/cassandra

Wish this helps.
regards,
Stanley Huang

[Level 2] how to extract compressed file in Python.

2011-04-26T11:50:00.002+08:00

If you want to extract compressed file, you could use zipfile/tarfile to implement.

[zip]

#!/bin/env python
import sys, zipfile, os, os.path

def unzip_file_into_dir(file, dir):
    os.mkdir(dir, 0777)
    zfobj = zipfile.ZipFile(file)
    for name in zfobj.namelist():
        if name.endswith('/'):
            os.mkdir(os.path.join(dir, name))
        else:
            outfile = open(os.path.join(dir, name), 'wb')
            outfile.write(zfobj.read(name))
            outfile.close()

unzip_file_into_dir('/tmp/mywar.war', '/tmp/mywar')

[tar.gz]

#!/bin/env python
import tarfile

def extract_file(file_name):
    tar_file=tarfile.open(name=file_name, mode='r:gz')
    tar_file.extractall()
    tar_file.close()
    pass

extract_file('/tmp/mytar.tar.gz')

Wish this helps.
regards,
Stanley Huang

[Level 2] Convert keys between openssh and openssl

2011-04-26T11:35:00.002+08:00

A good artical for convert keys between openssh and openssl.

http://sysmic.org/dotclear/index.php?post/2010/03/24/Convert-keys-betweens-GnuPG,-OpenSsh-and-OpenSSL

Wish this helps.
regards,
Stanley Huang

[Level 2] How to create multi instances of tomcat.

2011-04-25T22:33:00.003+08:00

If you want to create multi instances of tomcat,
you can follow the below steps:
1. Copy logs, conf and webapps from origianl tomcat foler to a new location.
# mkdir -p [a new folder you like]
# cd [original tomcat folder]
# cp ./logs ./conf ./webapps [a new folder you like]
2. Set environment variables "CATALINA_BASE" to the above path.
# export CATALINA_BASE=[a new folder you like]
3. Startup tomcat.
# [original tomcat folder]/bin/startup.sh

And what's the difference between CATALINA_BASE and CATALINA_HOME,
the decision path is as below:
1. Decide CATALINA_BASE:
If you have CATALINA_BASE, the base directory is CATALINA_BASE,
if not, the real path of binary would be the CATALINA_BASE.
2. Decide CATALINA_HOME:
If you have CATALINA_HOME, the webapps base directory is in CATALINA_HOME,
if not, the CATALINA_BASE would be the CATALINA_HOME.

Wish this helps.

regards,
Stanley Huang

[Level 2] How to display current line number in Python

2011-04-22T17:56:00.007+08:00

If you want to display current line number for debug info.
You can import inspect to do that.

#!/bin/env python
import inspect
from inspect import currentframe, getframeinfo

def getFilenameLineno():
    frameinfo = getframeinfo(currentframe().f_back)
    return (frameinfo.filename, frameinfo.lineno)

def lineno():
    return inspect.currentframe().f_back.f_lineno

def curr_filename_lineno():
    return inspect.currentframe().f_back.f_code

if __name__ == '__main__':
    print lineno()
    # skip line with comment
    print curr_filename_lineno()
    
    print '%s(#%s)' % getFilenameLineno()

    print lineno()

$ ./testLineno.py 
16
<code object <module> at 0xb7509f50, file "/tmp/t.py", line 2>
/tmp/testLineno.py(#20)
22

Another way is to parse inspect.stack()
e.g.

#!/bin/env python
import inspect

def t():
    tt()

def tt():
    ttt()

def ttt():
    tttt()

def tttt():
    print inspect.stack()

if __name__ == '__main__':
    t()

$ ./test.py 
[(<frame object at 0x18ff820>, '/tmp/t.py', 14, 'tttt', ['    print inspect.stack()\n'], 0), (<frame object at 0x190d970>, '/tmp/t.py', 11, 'ttt', ['    tttt()\n'], 0), (<frame object at 0x18fe0c0>, '/tmp/t.py', 8, 'tt', ['    ttt()\n'], 0), (<frame object at 0x1924460>, '/tmp/t.py', 5, 't', ['    tt()\n'], 0), (<frame object at 0x18c7f70>, '/tmp/t.py', 17, '<module>', ['    t()\n'], 0)]

more info, please reference here.

Wish this helps. regards, Stanley Huang

[Level 2] How to use yield in Python

2011-04-22T12:59:00.004+08:00

yield in python just pass the control to the parent.
if a() call b(),
in b and execute yield,
the control would back to a() util a call generator.next()
e.g.

#!/bin/env python
def foo():
    print 'start foo()'       ## only pass here when first time call .next()
    n = 3 
    for i in range(1,n+1):
        print 'in for()'
        print 'yield %s' % i 
        yield i               ## return i when call .next()
        print 'back to for()' ## start from here when call .next() again

if __name__ == '__main__': 
    print 'start main'
    print 'init main vars'
    f = foo()
    i = 0 
    n = 10
    print 'f: %s' % f 
    print 'i: %s' % i 
    print 'n: %s' % n 
    
    for x in range(0,n): 
        print 'f.next()'
        i = f.next()
        print 'print i'
        print i
        print 'in main'
    print 'call again f.next()'
    print f.next()
    print 'exit'

$ ./t1.py 
start main
init main vars
f: <generator object foo at 0xb73e69dc>
i: 0
n: 10
f.next()
start foo()
in for()
yield 1
print i
1
in main
f.next()
back to for()
in for()
yield 2
print i
2
in main
f.next()
back to for()
in for()
yield 3
print i
3
in main
f.next()
back to for()
Traceback (most recent call last):
  File "./t.py", line 23, in 
    i = f.next()
StopIteration

#!/bin/env python
def foo():
  for i in range(0,5):
    yield i

f = foo()

for n in f:
  print 'n=%s' % n

$ ./t2.py 
n=0
n=1
n=2
n=3
n=4

Wish this helps. regards, Stanley Huang

[Level 1] Support utf-8 in python.

2011-04-22T12:39:00.001+08:00

If you got an error message like "SyntaxError: Non-ASCII character '\xe4' in file"
That means you have non-ascii character in your script.
And if you want to support utf-8, you could add the comment in your script.
e.g.

#!/bin/env python
# -*- coding: utf8 -*-

Wish this helps.
regards,
Stanley Huang

[Level 3] Unit test in Python.

2011-04-21T01:58:00.000+08:00

#!/usr/bin/env python
import os,sys
import unittest

class C(object):
    def __init__(self):
        self.name = 'stanley'
        self.age = 24

class TS(unittest.TestCase):
    ## set up
    def setUp(self):
        self.c = C()
        pass

    ## clean up
    def tearDown(self):
        pass

    def test_getName(self):
        print self.c.name

    def test_getAge(self):
        print self.c.age

    def test_getNameAge(self):
        c = C()
        print c.name
        print c.age 

    def runTest(self):
        print self.test_getName()
        print self.test_getAge()

    #def myTestModule():
    #    tests = ['test_getName', 'test_getAge']
    #    suite = unittest.TestSuite(map(TS, tests))
    #    unittest.TextTestRunner(verbosity=2).run(suite)

class TS1(unittest.TestCase):
    def setUp(self):
        self.c = C()
        pass

    def tearDown(self):
        pass

    def test_name1(self):
        print self.c.name

    def test_age1(self):
        print self.c.age

    def runTest(self):
        self.test_name1()
        self.test_age1()

class TS2(unittest.TestCase):
    def setUp(self):
        self.c = C()
        pass

    def tearDown(self):
        pass

    def test_name2(self):
        print self.c.name

    def test_age2(self):
        print self.c.age

    def runTest(self):
        self.test_name2()
        self.test_age2()

def getSuite1():
    suite = unittest.TestSuite()
    ## use suite.addTest()
    ## 1. only coudl pass instance
    ## 2. must have method named 'runTest()'
    ## 3. could also pass test method when instanize

    suite.addTest(TS('test_getName'))
    suite.addTest(TS('test_getAge'))
    ts1 = TS1()
    ts2 = TS2('test_age2')
    suite.addTest(ts1)
    suite.addTest(ts2)
    return suite

def getSuite2():
    tests = ['test_getName', 'test_getAge']
    return unittest.TestSuite(map(TS, tests))

def getSuite3():
    return unittest.TestSuite(map(TS, ['test_getName']))

def getSuite4():
    return unittest.TestSuite(map(TS, ['test_getAge']))

def getCompositeSuite34():
    return unittest.TestSuite([getSuite3(), getSuite4()])
    
if __name__ == '__main__':
    ## uncomment when want to test. ##
    ## way 1:
    #unittest.main()
    ## way 2:
    #suite = unittest.TestLoader().loadTestsFromTestCase(TS)
    #unittest.TextTestRunner(verbosity=2).run(suite)
    ## way 3:
    #suite = unittest.TestLoader().loadTestsFromTestCase(TS)
    #suite1 = getSuite1()
    #suite2 = getSuite2()
    #allsuites = unittest.TestSuite((suite1, suite2))
    #unittest.TextTestRunner(verbosity=2).run(suite1)
    #unittest.TextTestRunner(verbosity=2).run(suite2)
    #unittest.TextTestRunner(verbosity=2).run(allsuites)
    ## way 4:
    #suite = getCompositeSuite34()
    #unittest.TextTestRunner(verbosity=2).run(suite)

    ## main ##
    #c = C()
    #print 'hello %s(%s)' % (c.name, c.age)
    pass

Wish this helps. regards, Stanley Huang

[Level 2] Python variable scope (2).

2011-04-20T23:37:00.002+08:00

In previous, I wrote a paper for python variable scope.
This time, I try to use python native decorator to implement the same idea.

#!/usr/bin/env python
class C(object):
    def __init__(self):
        self._name = None
        self.__age = None

    @property
    def name(self):
        """I'm the 'name' propertage."""
        return self._name

    @name.setter
    def name(self, value):
        self._name = value

    @name.deleter
    def name(self):
        del self._name

    @property
    def age(self):
        pass

    @age.getter
    def age(self):
        return self.__age

    @age.setter
    def age(self, value):
        self.__age = value

if __name__ == '__main__':
    c = C() 
    c.name = 1 
    print c.name
    print c._name
    del c.name
    try:
        print c.name
    except AttributeError, e:
        #print "'C' object has no attribute '_name'"
        print e
    
    c.age = 2 
    print c.age
    try:
        print c.__age
    except AttributeError, e:
        #print "'C' object has no attribute '__age'"
        print e
    
    try:
        del c.age
    except AttributeError, e:
        #print "can't delete attribute"
        print e

$ ./testProperty.py 
1
1
'C' object has no attribute '_name'
2
'C' object has no attribute '__age'
can't delete attribute
$

Wish this helps. regards, Stanley Huang

[Level 3] Firewall in Ubuntu.

2011-04-01T17:38:00.003+08:00

install gui tool for ufw

# apt-get -y install gufw
# gufw

use command(ufw) to setting firewall, and the config file in in /lib/ufw/user.rules

# ufw deny from any to any port 22
# ls -al /lib/ufw/user.rules -rw-r----- 1 root root 1405 2011-04-01 17:36 /lib/ufw/user.rules
# ufw delete deny from any to any port 22
# ls -al /lib/ufw/user.rules -rw-r----- 1 root root 1263 2011-04-01 17:36 /lib/ufw/user.rules

my setting history (reference only):

ufw disable
ufw default deny
ufw logging ON
ufw enable
ufw allow 22/tcp
ufw allow proto tcp from 192.168.1.0/24 to 192.168.1.101 port 80

Wish this helps. regards, Stanley Huang

[Level 2] How to compare two list (array) in Python

2011-04-01T09:10:00.002+08:00

#!/bin/env python
a=[1,2,3]
b=[2,3,4]
set_a = set(a)
set_b = set(b)
print set_a.difference(set_b)
print set_b.difference(set_a)
print set_a.union(set_b)
print set_a.intersection(set_b)
# print set_a.difference(set_b)[0] ## fail, 'set' object does not support indexing
print list(set_a.difference(set_b))[0]

$ ./t.py
set([1])
set([4])
set([1, 2, 3, 4])
set([2, 3])
1

Wish this helps. regards, Stanley Huang

[Level 2] gtk shell script for gcc/vala

2011-03-30T20:50:00.002+08:00

1. get vala syntax from gnome

[gcc.sh]

#!/bin/bash -vx
output=`echo $1 | sed -e 's/\.c$//'`
gcc -Wall $1 -o $output `pkg-config --cflags --libs gtk+-2.0`
$output

$ alias gcc='./gcc.sh '
$ gcc ./1.helloworld.c

[valac.sh]

#!/bin/bash
output=`echo $1 | sed -e 's/\.vala$//'`
/usr/bin/valac --pkg gtk+-2.0 $1
$output

$ alias valac='./valac.sh '
$ valac ./3.helloworld.vala

Wish this helps. regards, Stanley Huang

[Level 2] How to verify instance has attribute (variables in global/local) or not.

2011-03-30T14:04:00.002+08:00

$ cat ./test1.py

#!/bin/env python
class myClass:
    def __init__(self):
        self.attr1 = 'a'
        self.attr2 = 'b'
    def checkAttr(self, sAttr):
        return hasattr(self, sAttr)

mc = myClass()
print hasattr(mc, 'attr1')
print hasattr(mc, 'attr2')
print hasattr(mc, 'attr3')
print mc.checkAttr('attr1')
print mc.checkAttr('attr2')
print mc.checkAttr('attr3')

$ ./test1.py
True
True
False
True
True
False

$ cat ./test2.py

#!/bin/env python
## cannot extract test locals() in another method! ##
def testExistInGlobal():
#    print globals()
#    print locals()
    print 'a in globals(): %s' % ('Ture' if 'a' in globals() else 'False')
    print 'b in globals(): %s' % ('Ture' if 'b' in globals() else 'False')
#    print 'a in locals(): %s' % ('Ture' if 'a' in locals() else 'False')
#    print 'b in locals(): %s' % ('Ture' if 'b' in locals() else 'False')

def inFunc1():
    a=2
    print a
    print b
    testExistInGlobal()
    print 'a in locals(): %s' % ('Ture' if 'a' in locals() else 'False')
    print 'b in locals(): %s' % ('Ture' if 'b' in locals() else 'False')

def inFunc2(b):
    b=3
    print a
    print b
    testExistInGlobal()

print '-' * 20
print 'before assign a=1, b=2'
testExistInGlobal()
print 'a in locals(): %s' % ('Ture' if 'a' in locals() else 'False')
print 'b in locals(): %s' % ('Ture' if 'b' in locals() else 'False')

a=1
b=2

print '-' * 20
print 'after assign a=1, b=2'
testExistInGlobal()
print 'a in locals(): %s' % ('Ture' if 'a' in locals() else 'False')
print 'b in locals(): %s' % ('Ture' if 'b' in locals() else 'False')

print '-' * 20
print 'test in method1, a=2'
inFunc1()

print '-' * 20
print 'test in method2, b=3'
inFunc2(b)

print '-' * 20
print 'back to main'
testExistInGlobal()
print 'a in locals(): %s' % ('Ture' if 'a' in locals() else 'False')
print 'b in locals(): %s' % ('Ture' if 'b' in locals() else 'False')
print a
print b

$ ./test2.py 
--------------------
before assign a=1, b=2
a in globals(): False
b in globals(): False
a in locals(): False
b in locals(): False
--------------------
after assign a=1, b=2
a in globals(): Ture
b in globals(): Ture
a in locals(): Ture
b in locals(): Ture
--------------------
test in method1, a=2
2
2
a in globals(): Ture
b in globals(): Ture
a in locals(): Ture
b in locals(): False
--------------------
test in method2, b=3
1
3
a in globals(): Ture
b in globals(): Ture
a in locals(): False
b in locals(): Ture
--------------------
back to main
a in globals(): Ture
b in globals(): Ture
a in locals(): Ture
b in locals(): Ture
1
2

Wish this helps. regards, Stanley Huang

[Level 2] get broadcast by Python

2011-03-23T14:05:00.000+08:00

I wrote a python script to get broadcast by giving ip and netmask.
And I use "lambda" and "map" function to shorten my code,
so it's also a good sample for you to study map function.
More information please refer here.
The sample code is as the following:

#!/bin/env python
def getBroadcast(self, ip=None, netmask=None):
    if not ip or not netmask: return None
    ip = ip.split('.')
    netmask = netmask.split('.')
        
    for n in range(0,4):
        if netmask[n] == '0': break
    bc = (map(lambda a, b: str(int(a, 10)&int(b,10)), ip[0:n]+['255']*(4-n), netmask[0:n]+['255']*(4-n)))

    if n > 1: bc[n-1] = str(int(bc[n-1]) + (255 - int(netmask[n-1])))
    return '.'.join(bc)

print getBroadcast('192.168.10.1','255.255.254.0')

Result:

192.168.11.255

Wish this helps.
regards,
Stanley Huang

[Level 2] Get ether list by Python in Ubuntu.

2011-03-22T12:50:00.002+08:00

#!/bin/env python
def getEtherList():
    return [x.strip() for x in \
    os.popen("ifconfig -a | \
    grep Link | grep '^[^ ]' | \
    grep -v lo | awk '{print($1)}'")\
    .readlines()]

print getEtherList()

Wish this helps.
regards,
Stanley Huang

[Level 2] iif in Python

2011-03-21T16:17:00.004+08:00

If you want to implement iif in Python,
please refer to the following sample code:

#!/bin/env python
def iif(b):
  return 'It\'s True' if b else 'It\'s False'

print iif(True)
print iif(False)

And the following sample, guess what will you get!

#!/bin/env python
b = True
print 'b: %s' % 'Ture' if b else 'False'
b = False
print 'b: %s' % 'Ture' if b else 'False'

Result:

$ ./iif.py 
b: Ture
False

The second result is "False", not "b: False". Why???
Because the prededence of string concatenation is higher than if.
Therefore you need to use () to make it correct.

#!/bin/env python
b = True
print 'b: %s' % 'Ture' if b else 'False'
b = False
print 'b: %s' % ('Ture' if b else 'False')

Wish this helps.
regards,
Stanley Huang

[Level 2] Implement prefix and netmask transfer with Python.

2011-03-21T14:28:00.000+08:00

Sometimes, you want to transfer between netmask and prefix setting.
You can use the following sample script which is written by Python.

#!/bin/env python
def transferPrefix2Netmask(self, prefix):
        netmask = ''
        nLeadingFF = prefix / 8
        netmask += '.'.join(['255']*nLeadingFF)
        if nLeadingFF != 4:
            netmask += '.' + str(256-2**(8-prefix%8))
        nSuffix00 = 3 - nLeadingFF
        if nLeadingFF < 3:
            netmask += '.' + '.'.join(['00']*nSuffix00)
        return netmask
    
    def transferNetmask2Prefix(self, netmask):
        prefix = 0
        left = 0
        for i in netmask.split('.'):
            if i == '255':
                prefix += 8
            else:
                left = int(i)
                break
        if left != 0:
            for n in range(7, 0, -1):
                prefix += 1
                if left - 2 ** n == 0:
                    break
                else:
                    left -= 2 ** n
        return prefix

print transferPrefix2Netmask(16)
print transferPrefix2Netmask(17)
print transferPrefix2Netmask(18)
print transferPrefix2Netmask(22)
print transferPrefix2Netmask(23)
print transferPrefix2Netmask(24)
print transferNetmask2Prefix('255.255.128.0')
print transferNetmask2Prefix('255.255.192.0')
print transferNetmask2Prefix('255.255.252.0')
print transferNetmask2Prefix('255.255.254.0')
print transferNetmask2Prefix('255.255.255.0')
print transferNetmask2Prefix('255.255.255.128')
print transferNetmask2Prefix('255.255.255.192')
print transferNetmask2Prefix('255.255.255.252')
print transferNetmask2Prefix('255.255.255.254')
print transferNetmask2Prefix('255.255.255.255')

Wish this helps. regards, Stanley Huang

[Level 2] How to reset interface seq. no in Ubuntu

2011-03-21T14:25:00.000+08:00

If you want to reset the seq. no of interface.

You can remove the record in /etc/udev/rules.d/70-persistent-net.rules.

Wish this helps.
regards,
Stanley Huang

[Level 2] How to change passphrase by openssl in.

2011-03-19T22:31:00.002+08:00

If your company give you the key and cert for openvpn,
but somehow you want to change the passphrase that you easy to remember.
You can use openssl to modify the passphrase.
The sample as below:

Step 1, Remove the password:
# openssl rsa -in client.key -out client2.key

(It will prompt you for the current password, and write the decrypted key to client2.key)

Step 2, Add or change the password:
# openssl rsa -in client.key -out client2.key -des3

(It will prompt you for the current password (if any), then for the new password, and then write the encrypted key to client2.key)

Wish this helps.
regards,
Stanley Huang

[Level 3] How to implement "Press any key to continue" in Python.

2011-03-18T17:43:00.002+08:00

When you develop a terminal program,
you always need a function to "Press any key to continue",
but how to implement it in Python script.

I found out a sample on here:
http://code.activestate.com/recipes/134892/

Wish this helps.
regards,
Stanley Huang

[Level 3] How to implement reflection in Python.

2011-03-18T16:56:00.000+08:00

The Python Code:

#!/bin/env python
def f1():
  exec 'a=1'

def f2():
  exec 'global a; a=2'

def f3():
  print a

global a
a=0
f1()
f3()
f2()
f3()

The output is:

# /tmp/t.py 
0
2

As you can see,
The f1() function cannot modify global variable "a" successfully,
we can say that, exec is a fact function, and unless you declare a variable as a global variable,
or you just deal with a local variable. Like f1()
So if you want to deal with global variable,
please remember to declare it as the global variable first. Like f2()

Wish this helps. regards, Stanley Huang