中年人之碎碎念: 2010

2010年11月18日星期四

最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法

最近用Pidgin登入MSN會出現SSL憑證問題.
解決方法:
當然要先把Pidgin關掉.
download 新的授權憑證檔

here ===> http://files.andreineculau.com/projects/pidgin/omega.contacts.msn.com.txt

將 .txt 的副檔名拿掉, 存成 omega.contacts.msn.com 即可.
然後蓋掉原本在 ~/.purple/certificates/x509/tls_peers 目錄下的 omega.contacts.msn.com 檔案

然後再啟動Pidgin

2010年10月6日星期三

有意思的sql問題

在藍色小舖看到有人問一個sql問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD201010051423497Z5.html
我已經回答了.

use test;

CREATE TABLE `blue1` (
`data1` smallint(6) NOT NULL,
PRIMARY KEY (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

CREATE TABLE `blue2` (
`q1` smallint(6) NOT NULL,
`q2` smallint(6) NOT NULL,
`q3` smallint(6) NOT NULL,
KEY `fk1` (`q1`),
KEY `fk2` (`q2`),
KEY `fk3` (`q3`),
CONSTRAINT `fk1` FOREIGN KEY (`q1`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk2` FOREIGN KEY (`q2`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk3` FOREIGN KEY (`q3`) REFERENCES `blue1` (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

insert into blue1(data1) values (1),(2),(3),(4),(5);

insert into blue2 values
(1,3,4),
(2,1,5),
(3,4,1);

root@[test]>select count(q1) into @total from blue2;
Query OK, 1 row affected (0.00 sec)

root@[test]>select @total;
+--------+
| @total |
+--------+
| 3 |
+--------+

root@[test]>select q1, count(q1)/@total
-> from blue2
-> group by q1
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q1 from blue2);
+----+------------------+
| q1 | count(q1)/@total |
+----+------------------+
| 1 | 0.3333 |
| 2 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q2, count(q2)/@total
-> from blue2
-> group by q2
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q2 from blue2);
+----+------------------+
| q2 | count(q2)/@total |
+----+------------------+
| 1 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.3333 |
| 2 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+

root@[test]>select q3, count(q3)/@total
-> from blue2
-> group by q3
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q3 from blue2);
+----+------------------+
| q3 | count(q3)/@total |
+----+------------------+
| 1 | 0.3333 |
| 4 | 0.3333 |
| 5 | 0.3333 |
| 2 | 0.0000 |
| 3 | 0.0000 |
+----+------------------+

subquery 部份 select q3 from blue2 加上 distinct執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.

2010年9月16日星期四

Powerful tool for MySQL stored procedure developer.
我剛試用.
網址在:
http://www.mydebugger.com/index.php

2010年9月1日星期三

MySQL Database Quota Control

之前在酷學園有人問到,所以後來就開發了這功能.
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar

MySQL 讀取及輸出文字檔

在酷學園看到有人問到排序的問題,有兩欄位.
http://phorum.study-area.org/index.php/topic,62396.msg315492.html#new

推測是讀取儀表獲得的數據,要進行後續處理.若這類的需求只是偶爾,其實用試算表就很方便.但若是常態性需求,用程式是比較方便,排序/分組這些是資料庫的強項.
問題在於處理文字檔的方法,一般人較少知道.思考時就被侷限了,另外MySQL的文件
或是很多網站上的例子,輸入檔及輸出檔的檔名都是手動輸入固定的,顯得就是手工
作業. MySQL 的LOAD DATA指令不能在Stored Procedure,也不能prepare,彈性
受到限制,但是我們可以利用Shell Script.以下就是連grant privilege都有說明的範例,我也一併發到酷學園供發問者參考.

MySQL 讀取及輸出文字檔
===============================
1. 基本環境建立與初始測試

1.1 用 roo登入 mysql,建立作業用的資料庫與使用者,並賦予相關權限.

root@[(none)]>create database sorter;

root@[(none)]>grant all on sorter.* to 'sorter'@'%' identified by 'sorter';

root@[(none)]>grant file on *.* to 'sorter'@'%';

1.2 在OS中建立目錄供MySQL存取,並建立預備輸入的文字檔

ps. 目錄必須設定讓mysql有權限讀寫,不一定是讓mysql當owner

# cd /
# mkdir myimpexp
# chown mysql:mysql myimpexp

# cd myimpexp
# vim raw-2010-09-02
# cat raw-2010-09-02
10.2    25.2
10.2    50.4
10.2    100.8
20.4    25.2
20.4    50.4
20.4    100.8

ps. txt file裡面用tab隔開

1.3 用sorter登入並建立table

sorter@[sorter]>create table sortforge (
col1 decimal(8,2),
col2 decimal(8,2)
);

1.4 輸入文字檔

sorter@[sorter]>LOAD DATA INFILE '/myimpexp/raw-2010-09-02' INTO TABLE sortforge
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

sorter@[sorter]>select * from sortforge;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 10.20 | 25.20 |
| 10.20 | 50.40 |
| 10.20 | 100.80 |
| 20.40 | 25.20 |
| 20.40 | 50.40 |
| 20.40 | 100.80 |
+-------+--------+

sorter@[sorter]>select *
    -> from sortforge
    -> order by col2,col1 desc;
+-------+--------+
| col1 | col2   |
+-------+--------+
| 20.40 | 25.20 |
| 10.20 | 25.20 |
| 20.40 | 50.40 |
| 10.20 | 50.40 |
| 20.40 | 100.80 |
| 10.20 | 100.80 |
+-------+--------+

1.5 排序後輸出為文字檔

sorter@[sorter]>SET @OutfileCmd := concat("SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE '/myimpexp/sort-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".txt' FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';");
Query OK, 0 rows affected (0.00 sec)

sorter@[sorter]>PREPARE statement FROM @OutfileCmd;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

sorter@[sorter]>EXECUTE statement;
Query OK, 6 rows affected (0.00 sec)

# cat sort-2010-09-02_063030.txt
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

2. 使用Shell Script 呼叫處理
將上面的操作方式改用Shell Script並配合使用date.如欲排序之原始檔為儀器等輸出,
可以將檔案改名為raw-yyyy-mm-dd格式,並可將Shell Script配合cron進行自動化處理.

程式碼如下:
-------- script start ----------
#!/bin/bash
# ---- Basic info set up ----
MYSQL_PATH="/opt/mysql554/bin/mysql"
MYSQL_USER="sorter"
MYSQL_PASS="sorter"
MYSQL_DB="sorter"
# ---- Chnage the basic info for your environment

# Query MySQL database function
query() {
echo "$1" | ${MYSQL_PATH} -s --user=${MYSQL_USER} --password=${MYSQL_PASS} ${MYSQL_DB}
}

today=$( date +%F )
in_file="'/myimpexp/raw-$today'"

moment=$( date +%F_%H%M%S )
out_file="'/myimpexp/sort-$moment'"

sql="truncate sortforge; LOAD DATA INFILE $in_file INTO TABLE sortforge FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n'; SELECT col1,col2 FROM sortforge ORDER BY col2,col1 DESC INTO OUTFILE $out_file FIELDS TERMINATED BY '\t' ESCAPED BY '\"' LINES TERMINATED BY '\n';"

RESULT=$(query "${sql}")

----------script end ----------------

# ./sh1.sh
# cat sort-2010-09-02_064313
20.40   25.20
10.20   25.20
20.40   50.40
10.20   50.40
20.40   100.80
10.20   100.80

得到排序後的輸出

2010年8月7日星期六

MySQL database size part2

3. MySQL metadata 的存放空間 information_schema 在database size上的應用

user:mysample 有權限的database有三個,其中information_schema是系統metadata的存放空間,
我們就可以存取他來獲得一些資訊.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysample           |
| test               |
+--------------------+

我們可以執行下面的指令:
select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| mysample     |            37750 |             38912 |   76662 |
| test         |              152 |              2048 |    2200 |
+--------------+------------------+-------------------+---------+
2 rows in set (0.01 sec)

結果均相符,因為來源都一樣. 這裡可以看到MySQL方便的地方,我們不需要將information_schema排除.
到目前都是以user:mysample來作例子.當我們要以整個instance為範圍來管理就需要用root權限登入了.
以root的視野來看:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+

mysql> select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
    -> from information_schema.tables
    -> where table_type='BASE TABLE'
    -> and engine='MyISAM'
    -> group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| auth         |              231 |              4096 |    4327 |
| bookmarks    |              152 |              3072 |    3224 |
| books        |             6094 |             10240 |   16334 |
| joomla       |           105223 |            118784 | 224007 |
| joomla2      |           108588 |            118784 | 227372 |
| mail         |             3545 |              4096 |    7641 |
| mysample     |            37750 |             38912 |   76662 |
| mysql        |           492362 |             72704 | 565066 |
| pureftpd     |               88 |              3072 |    3160 |
| simple_blog |               44 |              3072 |    3116 |
| test         |              152 |              2048 |    2200 |
| test2        |              476 |              7168 |    7644 |
+--------------+------------------+-------------------+---------+

有沒有發覺到上面 show databases的結果扣掉 information_schema,共有14個databses;
而我們依據 information_schema.tables 計算的資料卻只有12筆.

因為有些database建立了,但是都沒有在裡面建立資料庫物件(table/view等),所以在
information_schema.tables 裡面就沒有資料.
接下來我們看一下 information_schema.schemata

mysql> select distinct schema_name
    -> from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
15 rows in set (0.00 sec)

mysql> select distinct table_schema
    -> from information_schema.tables;
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
13 rows in set (0.03 sec)

現在應該很清楚的看到了 information_schema.schemata, information_schema.tables
與show databases, show table status, show tables的關係了.

***********************************************************************************
4. 建立控制用database及其附屬table/view
接下來我們建立一個database 來作控制之用.

login as root

mysql> create database quotadb;

mysql> use quotadb;
Database changed

CREATE TABLE quota (
dbname CHAR(64) NOT NULL PRIMARY KEY,
limitbyte BIGINT NOT NULL,
exceeded ENUM('Y','N') DEFAULT 'N' NOT NULL);

insert into quota
select distinct schema_name,20971520,'N'
from information_schema.schemata
where schema_name != 'information_schema'
and schema_name != 'mysql'
and schema_name != 'quotadb';

mysql> select * from quota;
+-------------+-----------+----------+
| dbname      | limitbyte | exceeded |
+-------------+-----------+----------+
| auth        | 20971520 | N        |
| bookmarks   | 20971520 | N        |
| books       | 20971520 | N        |
| impexp_text | 20971520 | N        |
| joomla      | 20971520 | N        |
| joomla2     | 20971520 | N        |
| mail        | 20971520 | N        |
| mysample    | 20971520 | N        |
| pandora     | 20971520 | N        |
| pureftpd    | 20971520 | N        |
| simple_blog | 20971520 | N        |
| test        | 20971520 | N        |
| test2       | 20971520 | N        |
+-------------+-----------+----------+
13 rows in set (0.00 sec)

我們需要將mysql這個系統database排除,information_schema系統metadata也需要排除,
quotadb自己也要排除,以免限制住自己.

接下來建立一個view以方便獲取database size,並且在view中事先將mysql,quotadb排除.

create view v_dbsize as
select table_schema as dbname,sum(data_length) as data_size,sum(index_length) as index_size,sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema != 'mysql'
and table_schema != 'quotadb'
and table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;

mysql> select * from v_dbsize;
+-------------+-----------+------------+---------+
| dbname      | data_size | index_size | totsize |
+-------------+-----------+------------+---------+
| auth        |       231 |       4096 |    4327 |
| bookmarks   |       152 |       3072 |    3224 |
| books       |      6094 |      10240 |   16334 |
| joomla      |    105223 |     118784 | 224007 |
| joomla2     |    108588 |     118784 | 227372 |
| mail        |      3545 |       4096 |    7641 |
| mysample    |     37750 |      38912 |   76662 |
| pureftpd    |        88 |       3072 |    3160 |
| simple_blog |        44 |       3072 |    3116 |
| test        |       152 |       2048 |    2200 |
| test2       |       476 |       7168 |    7644 |
+-------------+-----------+------------+---------+
11 rows in set (0.03 sec)

ps:因為這個instance裡面有兩個database impexp_text與pandora目前都是沒有任何資料的,
所以quota table有13筆record, v_dbsize view裡面有11筆record,是正常的.
運用quota與v_dbsize作一下比較.

select q.dbname, q.limitbyte, d.totsize, q.limitbyte - d.totsize as diff, d.totsize / q.limitbyte as percent
from quota q, v_dbsize d
where q.dbname = d.dbname;
+-------------+-----------+---------+----------+---------+
| dbname      | limitbyte | totsize | diff     | percent |
+-------------+-----------+---------+----------+---------+
| auth        | 20971520 |    4327 | 20967193 | 0.0002 |
| bookmarks   | 20971520 |    3224 | 20968296 | 0.0002 |
| books       | 20971520 |   16334 | 20955186 | 0.0008 |
| joomla      | 20971520 | 224007 | 20747513 | 0.0107 |
| joomla2     | 20971520 | 227372 | 20744148 | 0.0108 |
| mail        | 20971520 |    7641 | 20963879 | 0.0004 |
| mysample    | 20971520 |   76662 | 20894858 | 0.0037 |
| pureftpd    | 20971520 |    3160 | 20968360 | 0.0002 |
| simple_blog | 20971520 |    3116 | 20968404 | 0.0001 |
| test        | 20971520 |    2200 | 20969320 | 0.0001 |
| test2       | 20971520 |    7644 | 20963876 | 0.0004 |
+-------------+-----------+---------+----------+---------+

MySQL database size part1

MySQL Database size limit
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912

所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.

然後我們寫了以下的php 程式:
----------------------------------------------------------------
#!/usr/bin/php -q
mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample;");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
   $sum += $total;
    print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234

因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.

-------------------------------------------------------------------
#!/usr/bin/php -q
# MySQL database size summary version 2
# Only count MyISAM tables

mysql_connect("localhost","mysample","mysample");

$result = mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM';");

$sum = 0;

while($array = mysql_fetch_array($result)) {
    $total = $array['Data_length'] + $array['Index_length'];
    $sum += $total;
    print "--------------------------\n";
    printf("Table:%s\n", $array['Name']);
    printf("Data Size:%d\n", $array['Data_length']);
    printf("Index Size:%d\n", $array['Index_length']);
    printf("Total Size:%d\n", $total);
    printf("Total Rows:%d\n", $array['Rows']);
    printf("Engine:%s\n", $array['Engine']);
    printf("Avg Size Per Row:%d\n", $array['Avg_row_length']);
    print "--------------------------\n";
}

print "*********************\n";
printf("The Sum of all Tables:%d bytes\n", $sum);
printf("The Size of database in K bytes:%f\n", $sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234

與上面計算檔案的方式結果相同.

小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.

*************************************************************************************
2. 使用function來計算

剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.

將mysample各個MyISAM的Table列出table_name,data_length,index_length

select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

計算總和:

select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

執行結果:
+---------+
| totsize |
+---------+
|   76662 |
+---------+

跟上面的資料相符.

接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
    from information_schema.tables
    where table_schema = in_dbname
    and table_type = 'BASE TABLE'
    and engine = 'MyISAM';

open c;
fetch c into rtnSize;
close c;

RETURN rtnSize;

END$$

DELIMITER ;
-----------------------------------------------------------

這個測試用的user: mysample,除了對database:mysample有權限外,對database:test也有權限.
我們測試一下

mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
|              76662 |
+--------------------+
1 row in set (0.07 sec)

mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
|           2200 |
+----------------+
1 row in set (0.00 sec)

可以看到可以依照輸入的database name計算出使用的size.

2010年7月25日星期日

民國年與西洋年轉換之MySQL Function的運用

民國年與西洋年轉換之MySQL Function的運用

之前已經建立兩個民國年與西洋年之轉換函數,也進行了測試.接下來就使用此轉換函數來應對民國年的狀況來作探討.
-----------------------------------------------------
1. 假設原本的系統,是用民國年來存日期資料.

此一方式是較不好的,無法利用日期函數;我們就能利用之前的chi2jul()來將原本的資料改為西洋年格式存放.在前面已經介紹
過轉換方法.現在比較少系統會是以此方式(民國年來存日期資料),會設法改用西洋年格式存放.而進到下一段的情況.
-------------------------------------------------------------------------------------------------------------------
2. 系統已經使用西洋年存放日期資料,但系統部份功能仍需存取民國年.

此一情形在系統中實務應用上多半是以西洋年日期格式來作判斷,展現時轉換為西洋年與民國年同時,或以民國年方式展現,
例如介面與報表等.而介面與報表之程式較多,進行系統升級時,若有漏網之魚就會造成後續運作上的困擾;或是運用一些報表
系統時,增加運算功能會影響報表產生的速度.若我們能在MySQL直接產生,介面與報表系統均向MySQL存取就比較單純與且效率
較高.

2.1 使用 VIEW的方式
--------------------------
之前有一個測試的table: julchi , 我們根據他來產生一個view.

mysql> select * from julchi;
+------------+------------------------+------------+
| julian     | chinadate1             | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30   |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31   |
| 2000-01-01 | 民國89年01月01日       | 89-01-01   |
| 1912-01-01 | 民國1年01月01日        | 1-01-01    |
| 1911-12-31 | 民前1年12月31日        | 1-12-31    |
| 1900-06-30 | 民前12年06月30日       | 12-06-30   |
| 2010-07-02 | NULL                    | NULL       |
| 1985-05-25 | 民國74年05月25日       | 74-05-25   |
+------------+------------------------+------------+
9 rows in set (0.10 sec)

# ---- 可以觀察到此表已經先包含一個未使用 jul2chi()配合update轉換為民國日期的一筆紀錄. -----

mysql> CREATE VIEW v_julchi(julian, chidate1, chidate2) AS
    -> SELECT julian, jul2chi(julian,1), jul2chi(julian,2)
    -> FROM julchi;
Query OK, 0 rows affected (0.30 sec)

# ---- 其實只有取用julchi table的julian欄位. -----

mysql> select * from v_julchi;
+------------+------------------------+-----------+
| julian     | chidate1               | chidate2 |
+------------+------------------------+-----------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30 |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31 |
| 2000-01-01 | 民國89年01月01日       | 89-01-01 |
| 1912-01-01 | 民國1年01月01日        | 1-01-01   |
| 1911-12-31 | 民前1年12月31日        | 1-12-31   |
| 1900-06-30 | 民前12年06月30日       | 12-06-30 |
| 2010-07-02 | 民國99年07月02日       | 99-07-02 |
| 1985-05-25 | 民國74年05月25日       | 74-05-25 |
+------------+------------------------+-----------+
9 rows in set (0.09 sec)

# ---- view 就能產生出民國年格式的資料 -----
# ---- 接下插入兩筆紀錄,就用民99年底與民100年初吧. -----

mysql> insert into julchi(julian)
    -> values (str_to_date('2010-12-31', '%Y-%m-%d'));
Query OK, 1 row affected (0.10 sec)

mysql> insert into julchi(julian)
    -> values (str_to_date('2011-01-01', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from v_julchi;
+------------+------------------------+-----------+
| julian     | chidate1               | chidate2 |
+------------+------------------------+-----------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30 |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31 |
| 2000-01-01 | 民國89年01月01日       | 89-01-01 |
| 1912-01-01 | 民國1年01月01日        | 1-01-01   |
| 1911-12-31 | 民前1年12月31日        | 1-12-31   |
| 1900-06-30 | 民前12年06月30日       | 12-06-30 |
| 2010-07-02 | 民國99年07月02日       | 99-07-02 |
| 1985-05-25 | 民國74年05月25日       | 74-05-25 |
| 2010-12-31 | 民國99年12月31日       | 99-12-31 |
| 2011-01-01 | 民國100年01月01日      | 100-01-01 |
+------------+------------------------+-----------+
11 rows in set (0.00 sec)

# ---- 可以看到結果是順利轉換,底下是 julchi,可以作為對照 -----

mysql> select * from julchi;
+------------+------------------------+------------+
| julian     | chinadate1             | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30   |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31   |
| 2000-01-01 | 民國89年01月01日       | 89-01-01   |
| 1912-01-01 | 民國1年01月01日        | 1-01-01    |
| 1911-12-31 | 民前1年12月31日        | 1-12-31    |
| 1900-06-30 | 民前12年06月30日       | 12-06-30   |
| 2010-07-02 | NULL                    | NULL       |
| 1985-05-25 | 民國74年05月25日       | 74-05-25   |
| 2010-12-31 | NULL                    | NULL       |
| 2011-01-01 | NULL                    | NULL       |
+------------+------------------------+------------+
11 rows in set (0.00 sec)

----------------------------------------------------------------------------
由上面的實做可以觀察到,使用view就能進行轉換,而且view的新欄位還能指定名稱.
當我們要在原本的系統連接時,就能使用view來靈活的中介,讓系統的前端介面與報表
的更動降到最低的程度.
例如可以將原本的table改名,而建立view使用原本table的名字等等方式.

==================================================

2.2 使用 Table + Trigger的方式
前面我們看到了使用 VIEW的方式,若是產生報表的程式頗多,且資料量也大,用VIEW的方式存取,
每次都需要進行運算,如此效率較差.
但是若是使用Table的方式,可以觀察上面的 julchi,插入新的紀錄,若在插入時沒有呼叫julchi()函數,
像是
INSERT INTO julchi VALUES(
str_to_date('2011-01-02', '%Y-%m-%d'), jul2chi(str_to_date('2011-01-02', '%Y-%m-%d'), 1), jul2chi(str_to_date('2011-01-02', '%Y-%m-%d'), 2));

而只是
mysql> insert into julchi(julian)
    -> values (str_to_date('2011-01-01', '%Y-%m-%d'));

就會在民國年的欄位有NULL值. 但若是原本沒有民國年欄位,是後來加上去的,則我們就要在原來的系統去找相關的SQL Command,都要修正,
如此一來工程浩大,且易有遺漏.若我們不想對原本的系統程式進行大規模的修正,則有下面兩種方式:
    2.2.1 使用定期 update 方式
         之前我們有介紹 UPDATE...SET 的方式

mysql> update julchi
    -> set chinadate1 = jul2chi(julian, 1);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 12 Changed: 3 Warnings: 0

mysql> update julchi
    -> set chinadate2 = jul2chi(julian, 2);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 12 Changed: 3 Warnings: 0

mysql> select * from julchi;
+------------+------------------------+------------+
| julian     | chinadate1             | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30   |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31   |
| 2000-01-01 | 民國89年01月01日       | 89-01-01   |
| 1912-01-01 | 民國1年01月01日        | 1-01-01    |
| 1911-12-31 | 民前1年12月31日        | 1-12-31    |
| 1900-06-30 | 民前12年06月30日       | 12-06-30   |
| 2010-07-02 | 民國99年07月02日       | 99-07-02   |
| 1985-05-25 | 民國74年05月25日       | 74-05-25   |
| 2010-12-31 | 民國99年12月31日       | 99-12-31   |
| 2011-01-01 | 民國100年01月01日      | 100-01-01 |
| 2011-01-02 | 民國100年01月02日      | 100-01-02 |
+------------+------------------------+------------+
12 rows in set (0.00 sec)

這樣就可以修正.但此一方式只適合在將原本的資料轉換到新的table時使用,而無法在系統平時運作時使用.
使用此方式,會進行table scan,效率差;需要執行此一UPDATE...SET指令後資料才會修正.不管是寫一個外部
程式,晚上11點時由工讀生啟動;或是利用cron table;或是利用MySQL的EVENT功能啟動,都會有問題.
當 julian 有變動時, 而系統修正的這兩道指令還沒執行時, chinadate1 與 chinadate2 卻還是舊資料,就會造成錯誤.

    2.2.2 Trigger方式
在使用Trigger以前,要先改權限.
在MySQL 5.1.6之前要使用trigger,必須要有 Super Privilege.

Super Privilege is global privilege.

mysql> GRANT SUPER ON *.* TO 'mysample'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user, Super_priv from user where Super_priv='Y';
+-----------------+----------+------------+
| host            | user     | Super_priv |
+-----------------+----------+------------+
| localhost       | root     | Y          |
| 127.0.0.1       | root     | Y          |
| %               | mysample | Y          |
+-----------------+----------+------------+

   INSERT Trigger:
我們先寫一個 INSERT Trigger.此tigeer呼叫了之前的jul2chi()
==================================================
DELIMITER $$

DROP TRIGGER IF EXISTS `mysample`.`tri_julchi_insert`$$
CREATE TRIGGER `mysample`.`tri_julchi_insert` BEFORE INSERT ON julchi FOR EACH ROW
BEGIN

SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);

END$$

DELIMITER ;
==================================================

測試看看:

mysql> insert into julchi(julian)
-> values (str_to_date('2011-01-04', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from julchi where julian='2011-01-04';
+------------+------------------------+------------+
| julian     | chinadate1             | chinadate2 |
+------------+------------------------+------------+
| 2011-01-04 | 民國100年01月04日      | 100-01-04 |
+------------+------------------------+------------+
1 row in set (0.00 sec)

可以正常轉換.
接下來就要再發展一個update trigger,這樣我們INSERT/UPDATE 只要以西洋年格式的julian欄位為主,而對應的chinadate1,chinadate2
兩個民國年欄位自動會跟著julian變化.
為了說明的方便,我們將建立一個新的Table julchi2,增加一個 dataid欄位,以方便辨識.

CREATE TABLE julchi2(
dataid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
julian DATE,
chinadate1 CHAR(18),
chinadate2 CHAR(18)
);

mysql> INSERT INTO julchi2(julian, chinadate1, chinadate2)
    -> SELECT julian,chinadate1,chinadate2
    -> FROM julchi;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> select * from julchi2;
+--------+------------+------------------------+------------+
| dataid | julian     | chinadate1             | chinadate2 |
+--------+------------+------------------------+------------+
|      1 | 2010-06-30 | 民國99年06月30日       | 99-06-30   |
|      2 | 2014-06-30 | 民國103年06月30日      | 103-06-30 |
|      3 | 1999-12-31 | 民國88年12月31日       | 88-12-31   |
|      4 | 2000-01-01 | 民國89年01月01日       | 89-01-01   |
|      5 | 1912-01-01 | 民國1年01月01日        | 1-01-01    |
|      6 | 1911-12-31 | 民前1年12月31日        | 1-12-31    |
|      7 | 1900-06-30 | 民前12年06月30日       | 12-06-30   |
|      8 | 2010-07-02 | 民國99年07月02日       | 99-07-02   |
|      9 | 1985-05-25 | 民國74年05月25日       | 74-05-25   |
|     10 | 2010-12-31 | 民國99年12月31日       | 99-12-31   |
|     11 | 2011-01-01 | 民國100年01月01日      | 100-01-01 |
|     12 | 2011-01-02 | 民國100年01月02日      | 100-01-02 |
|     13 | 2011-01-03 | 民國100年01月03日      | 100-01-03 |
|     14 | 2011-01-04 | 民國100年01月04日      | 100-01-04 |
+--------+------------+------------------------+------------+
14 rows in set (0.00 sec)

接下來要對新的table產生配合的trigger.

INSERT Trigger.跟剛才的一樣,只是在trigger名稱與作用的table名稱更改即可.
=================================================
DELIMITER $$

DROP TRIGGER IF EXISTS `mysample`.`tri_julchi2_bf_insert`$$
CREATE TRIGGER `mysample`.`tri_julchi2_bf_insert` BEFORE INSERT ON julchi2 FOR EACH ROW
BEGIN

SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);

END$$

DELIMITER ;
================================================
UPDATE Trigger.其實就是將作用的方式改為UPDATE,
當然是不同的trigger名稱.
===============================================
DELIMITER $$

DROP TRIGGER IF EXISTS `mysample`.`tri_julchi2_bf_update`$$
CREATE TRIGGER `mysample`.`tri_julchi2_bf_update` BEFORE UPDATE ON julchi2 FOR EACH ROW
BEGIN

SET NEW.chinadate1 = jul2chi(NEW.julian, 1);
SET NEW.chinadate2 = jul2chi(NEW.julian, 2);

END$$

DELIMITER ;
===========================================

接下來進行測試.

mysql> insert into julchi2(julian)
    -> values (str_to_date('2011-01-05', '%Y-%m-%d'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from julchi2;
+--------+------------+------------------------+------------+
| dataid | julian     | chinadate1             | chinadate2 |
+--------+------------+------------------------+------------+
|      1 | 2010-06-30 | 民國99年06月30日       | 99-06-30   |
|      2 | 2014-06-30 | 民國103年06月30日      | 103-06-30 |
|      3 | 1999-12-31 | 民國88年12月31日       | 88-12-31   |
|      4 | 2000-01-01 | 民國89年01月01日       | 89-01-01   |
|      5 | 1912-01-01 | 民國1年01月01日        | 1-01-01    |
|      6 | 1911-12-31 | 民前1年12月31日        | 1-12-31    |
|      7 | 1900-06-30 | 民前12年06月30日       | 12-06-30   |
|      8 | 2010-07-02 | 民國99年07月02日       | 99-07-02   |
|      9 | 1985-05-25 | 民國74年05月25日       | 74-05-25   |
|     10 | 2010-12-31 | 民國99年12月31日       | 99-12-31   |
|     11 | 2011-01-01 | 民國100年01月01日      | 100-01-01 |
|     12 | 2011-01-02 | 民國100年01月02日      | 100-01-02 |
|     13 | 2011-01-03 | 民國100年01月03日      | 100-01-03 |
|     14 | 2011-01-04 | 民國100年01月04日      | 100-01-04 |
|     15 | 2011-01-05 | 民國100年01月05日      | 100-01-05 |
+--------+------------+------------------------+------------+

可以看到INSERT Trigger發生效用了.

mysql> UPDATE julchi2
    -> SET julian=str_to_date('1985-06-06','%Y-%m-%d')
    -> WHERE dataid=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from julchi2 where dataid=9;
+--------+------------+-----------------------+------------+
| dataid | julian     | chinadate1            | chinadate2 |
+--------+------------+-----------------------+------------+
|      9 | 1985-06-06 | 民國74年06月06日      | 74-06-06   |
+--------+------------+-----------------------+------------+
1 row in set (0.00 sec)

可以看到UPDATE Trigger也發生效用了.

這樣我們就可以利用Trigger,增加新的民國欄位,供一些需要民國欄位的報表或介面來使用,
而原本系統裡面程式裡的INSERT/UPDATE也無須更動,保留原本的方式即可.如此就可節省大
量的時間,而且也不會有遺漏.
**********************************************
結論:
使用MySQL的store function來產生日期轉換函數,對原本舊系統更新到新的日期格式,可以方便的轉換.
轉換後需要民國年格式的報表或介面,也介紹了配合stroe function產生view的方式;以及使用
table配合trigger的方式.
對需要更改系統程式以對應民國100年,或是發展系統需要存取民國年格式,提供了以上兩種方法.
可以視狀況靈活使用.

也發表在酷學園討論區.

2010年7月7日星期三

Taiwan Weather Location Code

Taiwan Weather Location Code:
--------------------------------
TWXX0001 (Chang-hua) 彰化
TWXX0002 (Chia-i) 嘉義
TWXX0003 (Chi-lung) 基隆
TWXX0004 (Chingmei) 景美
TWXX0005 (Ch'i-shan) 旗山
TWXX0006 (Chu-tung) 竹東
TWXX0007 (Feng-yuan) 豐原
TWXX0008 (Hengch'un) 恆春
TWXX0009 (Hsin-chu) 新竹
TWXX0010 (Hsin-tien) 新店
TWXX0011 (Hua-lien) 花蓮
TWXX0012 (Kangshan) 岡山
TWXX0013 (Kao-hsiung) 高雄
TWXX0014 (Miao-li) 苗栗
TWXX0015 (P'ing-tung) 屏東
TWXX0016 (Su-ao) 蘇澳
TWXX0017 (Ta-cho-shui) 南澳 (大濁水)
TWXX0018 (Ta-fan-lieh) 南灣 (大阪埒)
TWXX0019 (T'ai-chung) 台中
TWXX0020 (T'ai-nan) 台南
TWXX0021 (Taipei) 台北
TWXX0022 (T'aipeihsien) 台北縣
TWXX0023 (T'aitung) 台東
TWXX0024 (Tan-shui) 淡水
TWXX0025 (T'ao-yuan) 桃園
TWXX0026 (Ta-wu) 大武
TWXX0027 (Hengchun) 恆春
TWXX0028 (Taidong) 台東

Linux安裝新字型

otf字型轉ttf字型
使用fontforge就能將otf轉ttf. openoffice 還是以ttf 字型為主.
所以還是轉成ttf字型較方便.

fontforge 要寫一個script比較好轉.
$ cat otf2ttf.sh
-------------------------------------
#!/usr/bin/fontforge
# Quick and dirty hack: converts a font to truetype (.ttf)
Print("Opening "+$1);
Open($1);
Print("Saving "+$1:r+".ttf");
Generate($1:r+".ttf");
Quit(0);
-----------------------
單檔轉換:
fontforge -script otf2ttf.sh FONTNAME.otf
多檔轉換:
for i in *.otf; do fontforge -script otf2ttf.sh $i; done

===================================

安裝新字型
[root@nana ~]# cd /usr/share/fonts
[root@nana fonts]# mkdir radiospace
[root@nana fonts]# cd radiospace/
[root@nana radiospace]# mv /home/vincent/ttf_font/*.ttf .
[root@nana radiospace]# ll
總計 204
-rw-rw-rw- 1 vincent vincent 29532 9月 2 2002 Radiofbi.ttf
-rw-rw-rw- 1 vincent vincent 33428 9月 2 2002 Radiofb.ttf
-rw-rw-rw- 1 vincent vincent 34528 9月 2 2002 Radiofc.ttf
-rw-rw-rw- 1 vincent vincent 29808 9月 2 2002 Radiofi.ttf
-rw-rw-rw- 1 vincent vincent 45632 9月 2 2002 Radiof.ttf
[root@nana radiospace]# pwd
/usr/share/fonts/radiospace
[root@nana radiospace]# fc-cache -v /usr/share/fonts/radiospace
/usr/share/fonts/radiospace: caching, 5 fonts, 0 dirs
/var/cache/fontconfig: cleaning cache directory
/var/cache/fontconfig: 3830d5c3ddfd5cd38a049b759396e72e-x86.cache-2: cache outdated: /usr/share/fonts
/root/.fontconfig: not cleaning unwritable cache directory
fc-cache: succeeded
安裝完成,列出字型
[root@nana radiospace]# fc-list

2010年7月6日星期二

紀錄Console 操作過程的相關命令, script 跟 tee. Script - record your command line input and output

紀錄Console 操作的相關命令, script 跟 tee.
對系統管理員來說,能把操作的過程保留下來,不管是工作紀錄或是學習來說都是很方便的.
script 使用很簡單, 直接打script.然後系統會出現:

Script started, file is typescript

告訴我們存檔名稱叫 typescript
接下來就繼續操作..... 要結束時輸入exit, 或是ctrl-d,然後系統會出現:

Script done, file is typescript

我們去查看 typescript, 前面還有紀錄起始時間,後面有紀錄結束時間.

當然我們也可以使用 script -a logfile

的方式來指定紀錄檔,這在使用幾個terminal時很有用.

----------------------------------------------------------

 tee 命令是同時將 stdout 的輸出轉到我們指定的file, 這在做make時很有用.或是其他指令都可.

make 2>&1 | tee make.log

這樣同時將 stdout , stderr 都轉向重導至 make.log

----------------------------------------------------------

mysql client 也有 --tee=file

這樣可以把操作過程都紀錄的功能.當然他是很忠實的把過程紀錄,不會幫我們添加起始時間.

不過我們只要先來個  select now(); 這樣就把時間紀錄下來了.

2010年6月30日星期三

民國年與西洋年轉換之MySQL Function

民國年與西洋年轉換之MySQL Function
=====================================
先開table吧.使用底下的sql command.
-----------------------
use mysample;

create table olddate(
id int auto_increment primary key,
olddate char(9),
newdate date
);

insert into olddate(olddate)
values(
'99/03/05'
);

insert into olddate(olddate)
values(
'101/10/04'
);
----------------------

再來就寫個function 將民國年轉成西洋年.程式碼如下:

----------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`chi2jul`$$
CREATE FUNCTION `mysample`.`chi2jul` (olddate char(9)) RETURNS date
BEGIN
declare str_len int;
declare cut_len int;
declare old_year int;
declare new_year int;
declare new_date_str char(10);
declare new_date date;

set str_len = length(olddate);

if str_len = 8 then
    set cut_len = 2;
else
    set cut_len = 3;
end if;

set old_year = convert(left(olddate, cut_len),unsigned integer);

set new_year = old_year + 1911;

set new_date_str = concat(cast(new_year as char), right(olddate, 6));

set new_date = cast(new_date_str as date);
/* we can also use str_to_date() and date_format()
    functions to transfer
*/
return new_date;

END$$

DELIMITER ;
---------------------------------
測試一下:
mysql> select id,chi2jul(olddate) from olddate;
+----+------------------+
| id | chi2jul(olddate) |
+----+------------------+
| 1 | 2010-03-05       |
| 2 | 2012-10-04       |
+----+------------------+
2 rows in set (0.00 sec)

結果正確!接下來直接轉換.

mysql> update olddate
    -> set newdate = chi2jul(olddate);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> select * from olddate;
+----+-----------+------------+
| id | olddate   | newdate    |
+----+-----------+------------+
| 1 | 99/03/05 | 2010-03-05 |
| 2 | 101/10/04 | 2012-10-04 |
+----+-----------+------------+
2 rows in set (0.00 sec)

成功轉換!
****************************************************

底下是產生到暫存table的方式 --->
產生一個暫存轉換用的table,sql 如下:
----------------------------
create table tmp_newdate(
id int,
newdate date
);

insert into tmp_newdate (id, newdate)
select id, chi2jul(olddate)
from olddate;

mysql> insert into tmp_newdate (id, newdate)
    -> select id, chi2jul(olddate)
    -> from olddate;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from tmp_newdate;
+------+------------+
| id   | newdate    |
+------+------------+
|    1 | 2010-03-05 |
|    2 | 2012-10-04 |
+------+------------+
2 rows in set (0.00 sec)

mysql> update olddate,tmp_newdate
    -> set olddate.newdate = tmp_newdate.newdate
    -> where olddate.id = tmp_newdate.id;
Query OK, 2 rows affected (0.15 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from olddate;
+----+-----------+------------+
| id | olddate   | newdate    |
+----+-----------+------------+
| 1 | 99/03/05 | 2010-03-05 |
| 2 | 101/10/04 | 2012-10-04 |
+----+-----------+------------+
2 rows in set (0.00 sec)

成功轉換!
*******************************************
有了民國年轉西洋年,再來要有配對的西洋年轉民國年的Function才完整.
此時要注意1911年及1911年以前的狀況.
1912年為民國1年,1911年為民前1年.
另外為了兼顧轉換時不需要民國yyyy年mm月dd日格式,所以有轉換型態之參數.
程式如下:
------------------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`jul2chi`$$
CREATE FUNCTION `mysample`.`jul2chi` (in_date DATE, in_trantype INT) RETURNS CHAR(18)
BEGIN
DECLARE date_char1 char(30);
DECLARE tran_date date;
DECLARE date_char char(10);
DECLARE cyear char(4);
DECLARE cmonth char(2);
DECLARE cday char(2);
DECLARE iyear int;
DECLARE rtn_date char(18);
DECLARE after_flag int default 1;
DECLARE charyear char(4) default '民國';

-- ************************************
-- vincent chang
-- ************************************
set date_char1 = cast(in_date as char);
set tran_date = str_to_date(date_char1, '%Y-%m-%d');
set date_char = date_format(in_date,'%Y-%m-%d');
-- Force transfered to YYYY-mm-dd format

set cyear = left(date_char,4);
set cmonth = substr(date_char,6,2);
set cday = right(date_char,2);

set iyear = convert(cyear, signed integer);
set iyear = iyear - 1911;

if iyear <= 0 then
   set after_flag = -1;
   set iyear = iyear - 1;
   set iyear = iyear * after_flag;
   set charyear = '民前';
end if;

if in_trantype = 1 then
   set rtn_date = concat(charyear,convert(iyear,char(4)),'年',
               cmonth,'月',cday,'日');
else
   set rtn_date = concat(iyear,'-',cmonth,'-',cday);
end if;

RETURN rtn_date;

END$$

DELIMITER ;
-----------------------------------
接下來進行測試.建立一個table,把一些特別的日子放進去.

use mysample;

create table julchi(
julian date,
chinadate1 char(18),
chinadate2 char(18)
);

insert into julchi(julian)
values (str_to_date('2010-06-30', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('2014-06-30', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('1999-12-31', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('2000-01-01', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('1912-01-01', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('1911-12-31', '%Y-%m-%d'));

insert into julchi(julian)
values (str_to_date('1900-06-30', '%Y-%m-%d'));
------------------------------------
mysql> update julchi
    -> set chinadate1 = jul2chi(julian,1);
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> update julchi
    -> set chinadate2 = jul2chi(julian,2);
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> select * from julchi;
+------------+------------------------+------------+
| julian     | chinadate1             | chinadate2 |
+------------+------------------------+------------+
| 2010-06-30 | 民國99年06月30日       | 99-06-30   |
| 2014-06-30 | 民國103年06月30日      | 103-06-30 |
| 1999-12-31 | 民國88年12月31日       | 88-12-31   |
| 2000-01-01 | 民國89年01月01日       | 89-01-01   |
| 1912-01-01 | 民國1年01月01日        | 1-01-01    |
| 1911-12-31 | 民前1年12月31日        | 1-12-31    |
| 1900-06-30 | 民前12年06月30日       | 12-06-30   |
+------------+------------------------+------------+
7 rows in set (0.00 sec)

轉換成功!

2010年6月18日星期五

FCron : Vixie Cron的替代品

現在CentOS系統上的cron,是vixie cron, Paul Vixie寫的.有一些其他的cron程式可以提供
更多功能,例如fcron
http://fcron.free.fr/
也可以精確到秒.或是視系統loading調整.
另外有前端GUI,協助我們使用,fcronq
http://code.google.com/p/fcronq/
http://sourceforge.net/projects/fcronq/
是用python開發的

cron with date tip

If need to run a job on the 3rd Wednesday of every month, but cron won’t let me do this. Run every day in the 3rd week, but then check that the day is a Wednesday. e.g.

00 02 15-21 * * if [ `date +\%u` -eq 3 ] ; then run_myjob; fi

Note that the \ is used to escape the %, otherwise cron treats it as a newline.

2010年6月17日星期四

start-stop-daemon on CentOS

有些package會使用start-stop-daemon 來啟動/停止 daemon,例如Secrond就是.
但是這是Debian/Ubuntu 的,在RHEL/Fedora/CentOS裡沒有,這樣就會發生錯誤,
造成無法正常控制Daemon. 我們可以修改/etc/init.d/daemon-script
但是這樣以後每個使用此機制的都要修改,也是麻煩.那我們要生一個這個指令出來,
有人是找一台Debian copy過來.其實在我們的CentOS裡面已經有source code了.
先檢查一下sysvinit有沒有安裝,一般都是有的啦.然後在 /usr/share/doc/SysVinit-2.86
裡面有 start-stop-daemon.c
這樣就可以gcc start-stop-daemon.c -o start-stop-daemon
然後 cp start-stop-daemon /usr/sbin
這世界沒那麼美好..... compile時會發生錯誤 (>"<)
我已經修正好程式了, 按我download
用這個compile 就可以用了,因為我沒有做什麼了不起的修改,所以都沒有在source code裡附加上名字,都是原作者的名字.

可以精確到秒的cron daemon : Secrond , cron handling tasks in intervals of seconds

cron是系統管理的好幫手,但是只能設定到分鐘.網路上有些是用shell script / sleep
方法來設法達到秒,但是sleep會有誤差的.
有人就開發了可以精確到秒的cron wrap daemon.
Secrond:
https://launchpad.net/secrond

目前是0.41版.
安裝以後會有說明:
----------------------------
Installing init script at /etc/init.d/secrond ...
Creating settings directory at /etc/secrond ...
Installing schedule file at /etc/secrond/schedule ...
EDIT schedule in: /etc/secrond/schedule
START secrond with: /etc/init.d/secrond start
RELOAD schedule with: /etc/init.d/secrond reload
-----------------------------
這樣我們就知道怎樣設定跟啟動daemon.

接下來就寫個 shell script來測試一下:
script name: testsecron.sh
內容很簡單,就是touch一個file, file name format要有時間戳.
如下:
======================
#!/bin/bash
touch /home/vincent/myshell/test_`date +%Y:%m:%d-%H:%M:%S`
======================
然後到/etc/secrond/
vim schedule
======================
# SECOND(0-59) MINUTE(0-59) HOUR(0-23) DAY(1-31) COMMAND
10 * * * /home/vincent/myshell/testsecron.sh
======================
檔案第一行就有format的說明,我們就設定精確到逢每分鐘的10秒就執行剛才的script.
接下來當然要把daemon啟動.
然後去喝杯咖啡,抽根煙.再來檢查執行結果:
-rw-r--r-- 1 root root 0 6月 18 10:58 test_2010:06:18-10:58:10
-rw-r--r-- 1 root root 0 6月 18 10:59 test_2010:06:18-10:59:10
-rw-r--r-- 1 root root 0 6月 18 11:00 test_2010:06:18-11:00:10
-rw-r--r-- 1 root root 0 6月 18 11:01 test_2010:06:18-11:01:10
-rw-r--r-- 1 root root 0 6月 18 11:02 test_2010:06:18-11:02:10
--------------------
效果不錯 (^.^)
===========================
再來試試一次每隔20秒執行一次.
到/etc/secrond/
vim schedule
=========================================================
# SECOND(0-59) MINUTE(0-59) HOUR(0-23) DAY(1-31) COMMAND
10,30,50 * * * /home/vincent/myshell/testsecron.sh
-------------------------------------------
# service secrond start
Starting periodic command scheduler secrond
-------------------------------------------
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:10
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:30
-rw-r--r-- 1 root root 0 6月 22 19:43 test_2010:06:22-19:43:50
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:10
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:30
-rw-r--r-- 1 root root 0 6月 22 19:44 test_2010:06:22-19:44:50
-rw-r--r-- 1 root root 0 6月 22 19:45 test_2010:06:22-19:45:10
-rw-r--r-- 1 root root 0 6月 22 19:45 test_2010:06:22-19:45:30

果然就每隔20秒執行一次了.

訂閱：文章 (Atom)

2010年11月18日 星期四

2010年10月6日 星期三

2010年9月16日 星期四

2010年9月1日 星期三

2010年8月7日 星期六

2010年7月25日 星期日

2010年7月7日 星期三

2010年7月6日 星期二

2010年6月30日 星期三

2010年6月18日 星期五

2010年6月17日 星期四

搜尋此網誌