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(); 這樣就把時間紀錄下來了.
張貼者： 於 沒有留言:
標籤： , ,
訂閱： 文章 (Atom)