DB2 学习笔记

I had a plugin that allows having a related entries list in the same category. It's not very helpfule for visitors as you have many entries in a category.

Tags is supported since MT 3.3. We may want to have a related entries list based on tags on the current entry. TagSupplementals plugin is doing that. It provides a set of features for tags supplement. We're using one of them.

The too long monthly archive list get bad-looking.

Download a new plug-in mt-archive-dateheader to have a neat calendar for monthly archive like this:

Change the template to

  <h2>按月归档|By Month</h2>

  <a class="skip" xhref="http://blog.alex.com/cgi-bin/mt/mt.cgi#endbymonth">Skip over by month</a>

  <MTArchiveList archive_type="Monthly">
  <MTArchiveDateHeader>
    <p><b><MTArchiveDate format="%Y"></b></p>
  </MTArchiveDateHeader>
  <a xhref="<$MTArchiveLink$>"><MTArchiveDate format="%B"></a><span class="num"><$MTArchiveCount$></span> 
  </MTArchiveList>
  <p></p>
  
  <a class="skip" id="endbymonth"></a>

Connect to db mt

mysql> use mt

List DB or tables

mysql> show databases;
mysql> show tables;

Backup DB mt

dos> mysqldump -u root -p mt > dbname.sql;

Query table mt_entry structure 

mysql> show create table mt_entry;

最近决定把MT的后台数据从Berkeley的文件DB转到MySQL。原因之一是使用关系数据库可以获得更多的灵活性，比如运行一条sql来变更 所有entry的某一个属性；另外一个原因是为了提前熟悉一下这个数据库，牙牙网站使用虚拟主机，DreamHost提供的数据库是MySQL，而我从前 主要使用了SQL Server，Oracle和DB2，Postgresql也只了解了一点，而MySQL则几乎没有接触过。

下载了5.0.27安装文件，安装顺利，使用了第一个默认端口3306，UTF-8的数据库字符集。

安装完成，运行了命令行客户端，输入help，给出的帮助命令不多，也没见到有关数据库创建的命令，便查了安装的帮助文件，组织的还算不错，很齐全，很快找到相关的命令：

查看已有数据库
show databases;
连接数据库 test
use test;
创建数据库 mt
create database mt;

接下来寻找把已有DB转化到MySQL的方法，很快找到一个第三方的工具mt-db-convert.cgi ，可以实现MT支持的几种不同的DB之间的转换。

下载回来放到cgi-bin的mt目录，在浏览器访问运行，输入新创建的MySQL数据库名称mt，用户名root，密码，主机填入localhost，开始Convert。转换过程很快就完成了，不到1分钟，没有出现错误提示，转换信息如下：

mt-db-convert.cgi($Rev: 173 $): Converting your MT data between DB engines (for MT 3.2)
Loading database schema...                                                                                    
Loading data...                                                                                               
MT::Author                                                                                                    
.                                                                                                             
(1 objects saved.)                                                                                            
MT::Blog                                                                                                      
..                                                                                                            
(2 objects saved.)                                                                                            
MT::Trackback                                                                                                 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......                                                                                 
(327 objects saved.)                                                                                          
MT::Category                                                                                                  
.......... .......... .......... .......... ..........                                                        
(50 objects saved.)                                                                                           
MT::Comment                                                                                                   
..                                                                                                            
(2 objects saved.)                                                                                            
MT::Entry                                                                                                     
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... ....                                                                                               
(514 objects saved.)                                                                                          
MT::IPBanList                                                                                                 
(0 objects saved.)                                                                                            
MT::Log                                                                                                       
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......                                                                      
(437 objects saved.)                                                                                          
MT::Notification                                                                                              
(0 objects saved.)                                                                                            
MT::Permission                                                                                                
....                                                                                                          
(4 objects saved.)                                                                                            
MT::Placement                                                                                                 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......... .......... .......... .......... .......... .......... .......... .......... 
.......... .......... .......                                                                                 
(527 objects saved.)                                                                                          
MT::Template                                                                                                  
.......... .......... .......... .......... .......... .......... .......... .......... ...                   
(83 objects saved.)                                                                                           
MT::TemplateMap                                                                                               
..........                                                                                                    
(10 objects saved.)                                                                                           
MT::TBPing                                                                                                    
(0 objects saved.)                                                                                            
MT::Session                                                                                                   
.......... .......... .........                                                                               
(29 objects saved.)                                                                                           
MT::PluginData                                                                                                
(0 objects saved.)                                                                                            
MT::Config                                                                                                    
.                                                                                                             
(1 objects saved.)                                                                                            
MT::FileInfo                                                                                                  
(0 objects saved.)                                                                                            
Done copying data from DBM to DBI::mysql! All went well.                                                      
Your recommended setting                                                                                      
-------------------------------------                                                                         
# DataSource R:/SITE/mt-db                                                                                    
ObjectDriver DBI::mysql                                                                                       
Database mt                                                                                                   
DBUser root                                                                                                   
DBHost localhost                                                                                              
DBPassword comein                                                                                             
-------------------------------------                                                                         

然后修改mt-config.cgi文件，把数据源改成MySQL。 

再次访问MT，看起来一切正常，甚至原有的session都没有丢掉，只是后台使用的数据已经改变了位置。
不过发现了中文乱码问题，Google到了这个方法 ，按文修改后，需要再次执行mt-db-convert.cgi，把数据重新导入MySQL，再访问MT，乱码没有了，所有中文显示正常。

转换后又发现了一个新的问题，原DB中的Tags全部丢失，原因是mt-db-convert.cgi仅支持到MT 3.1，3.3的新功能带来的数据变化可能还会有其它信息丢失。

NOTE

This script is compatible with Movable Type version 3.1x. I didn't test it at any other versions.

其实MT自身已经提供了转换工具mt-db2sql.cgi，与mt.cgi在同一目录当中。使用方法是，安装好MySQL数据库服务，并创建好用于MT的DB，将MySQL数据库的配置信息加入mt-config.cgi，并同时保留原Berkeley DB的路径配置，即保持两种DB的配置同时生效，然后通过浏览器运行mt-db2sql.cgi，转换会自动开始，显示如下信息：

Loading database schema...
Loading data...
MT::Author
1
MT::Blog
1
3
MT::Trackback 
...
Done copying data from Berkeley DB to your SQL database! All went well.

确认转换成功后，再修改mt-config.cgi，注释掉Berkeley DB的配置即可。

这里是官方的帮助文档：

• Databases: Database Conversion

• 李小龙语录
• 论李小龙对传统哲学的新整合
• 武道释义  
• 振藩截拳道的精华元素

There's no way to batch edit basename in MT, even no a plugin for this.

I hacked into .tmpl and .pm files to implement the feature based on MT version 3.33.

• \cgi-bin\mt\tmpl\cms\entry_table.tmpl
  

line 40 add:

<TMPL_IF NAME=IS_POWER_EDIT>
<th id="en-basename"><MT_TRANS phrase="Basename"></th>
</TMPL_IF> 

line 133 add: 

<TMPL_IF NAME=IS_POWER_EDIT>
<td>
<input type="hidden" name="basename_manual_<TMPL_VAR NAME=ID>" id="basename_manual_<TMPL_VAR NAME=ID>" value="0" />
<input type="text" name="basename_<TMPL_VAR NAME=ID>" id="basename_<TMPL_VAR NAME=ID>" value="<TMPL_VAR NAME=BASENAME>" onchange="setElementValue('basename_<TMPL_VAR NAME=ID>', dirify(this.value));setElementValue('basename_manual_<TMPL_VAR NAME=ID>', 1)" />
</td>
</TMPL_IF> 

• \cgi-bin\mt\lib\MT\App\CMS.pm

line 6468 add:

        my $basename = $q->param('basename_' . $id);
if ( $basename && $q->param('basename_manual_' . $id)) {
$entry->basename($basename);
} elsif ( !$basename ) {
$entry->basename(MT::Util::make_unique_basename($entry));
}

• \mt-static\style.css add(optional):

line 1107 add:

 #list-entry table #en-basename { width: 20% }

Download MT 3.33 from MovableType.

I didn't find the special version for upgrade,  however get it back and unzip.

Out of my expectation, there's no any document about this release, like Readme.txt or so. Anyway I've experienced 2 times upgrade with past version.

Rename old cgi-bin directory as old, then upzip new as the original name. Make a compare by Beyond Compare, Rename mt-config.cgi-original to mt-config.cgi, and merge the old settings into it. The new configuration file remove most optional settings, only keep the required settings.

Modify the first line of all .cgi files from:

#!/usr/bin/perl -w

to

#!d:/green/perl/bin/perl -w

Rename old mt-static as mt-static_old, and move the new one to the path.

Open Firefox, address to http://blog.alex.com/cgi-bin/mt/mt.cgi, upgrade prompted automatically, Confirm to upgrade.

    * Upgrading database from version 3.2001.
    * Upgrading table for MT::Log
    * Upgrading table for MT::Category
    * Creating new template: 'Search Results Template'.
    * Assigning basename for categories... (100%)
    * Migrating any "tag" categories to new tags... (100%)
    * Setting new entry defaults for weblogs... (100%)
    * Updating user permissions for editing tags... (100%)
    * Database has been upgraded to version 3.3.

The new version works now.

Remain some work, need to restore the HTML Editor and some plugins from old version.

2006-11-20  Update

There're 3 plugins I used before update.

• Collect
• RelatedEntries
• TinyMCE

Collect and RelatedEntries are easy to restore. TinyMce was intergreted via modifying tmpl files by myself more complex than a true plugin. I found there's new plugin comes,  EnhancedEntryEditing v1.11. TinyMCE v2.01 is wrapped in it. It' easy to install as well as normal plugins. See the online manual .

EnhancedEntryEditing provides less features as default setting than TinyMCE. I made change to support more features.

1. Replace the content of below directory with the TinyMCE 2.02 to let it have the newest features.
   
• EnhancedEntryEditing_1.11\EnhancedEntryEditing\mt-static\plugins\Ajaxify\tinymce
2. Modify the plugin setting.
   

This step is more complex because of the version conflict. What I did is:

• Comment out all settings from the EnhancedEntryEditing plugin(with TinyMCE 2.01)
  
• Copy all settings from my old TinyMCE 2.02 as the current settings.
• Merge the special settings in EnhancedEntryEditing to the current.

ore,excerpt",
        //theme : "advanced",
        //plugins : "iespell, emotions, inlinepopups",
        //theme_advanced_blockformats : "p,h1,h2,h3,h4,h5,h6",
        //theme_advanced_buttons1 : "formatselect,bold,italic,underline,strikethrough,separator,justifyleft,justifycenter,justifyright,justifyfull,separator,bullist,numlist,outdent,indent,separator,undo,redo,separator,link,unlink,separator,image,emotions,iespell,help",
        //theme_advanced_buttons2 : "",
        //theme_advanced_buttons3 : "",
        //theme_advanced_toolbar_location : "top",
        //theme_advanced_toolbar_align : "left",
        //extended_valid_elements : "a[name|href|target|title|onclick],img[class|src|border=0|alt|title|hspace|vspace|width|height|align|onmouseover|onmouseout|name],hr[class|width|size|noshade],span[class|align|style]",
        //force_p_newlines : true,
        //relative_urls : false,
        //remove_script_host : false,
        //button_tile_map : true,
        //ask : false,
        //auto_cleanup_word : true,
        //theme_advanced_path_location : "bottom",
        //theme_advanced_resizing : true,
        //theme_advanced_resize_horizontal : false,
        //safari_warning: false,
        //oninit: "quicktagsHide"

        //mode : "textareas",
        mode : "exact",
        elements : "text,text_more",
        //editor_selector : "mceEditor",
        theme : "advanced",
        //plugins : "table,save,advhr,advimage,advlink,emotions,iespell,insertdatetime,preview,zoom,flash,searchreplace,print,contextmenu",
        plugins : "table,advhr,advimage,advlink,emotions,iespell,insertdatetime,preview,zoom,flash,searchreplace,print,contextmenu",
        //theme_advanced_buttons1_add_before : "save,separator",
        theme_advanced_buttons1_add : "fontselect,fontsizeselect",
        theme_advanced_buttons2_add : "separator,insertdate,inserttime,preview,zoom,separator,forecolor,backcolor",
        theme_advanced_buttons2_add_before: "cut,copy,paste,separator,search,replace,separator",
        theme_advanced_buttons3_add_before : "tablecontrols,separator",
        theme_advanced_buttons3_add : "emotions,iespell,flash,advhr,separator,print",
        theme_advanced_toolbar_location : "top",
        theme_advanced_toolbar_align : "left",
        theme_advanced_path_location : "bottom",
        plugin_insertdate_dateFormat : "%Y-%m-%d",
        plugin_insertdate_timeFormat : "%H:%M:%S",
        extended_valid_elements : "a[name|href|target|title|onclick],img[class|src|border=0|alt|title|hspace|vspace|width|height|align|onmouseover|onmouseout|name],hr[class|width|size|noshade],font[face|size|color|style],span[class|align|style]",
        external_link_list_url : "example_data/example_link_list.js",
        external_image_list_url : "example_data/example_image_list.js",
        flash_external_list_url : "example_data/example_flash_list.js",
        content_css : "/css/screen.css",
        force_p_newlines : true,
        relative_urls : false,
        remove_script_host : false,
        button_tile_map : true,
        ask : false,
        auto_cleanup_word : true,
        theme_advanced_resizing : true,
        theme_advanced_resize_horizontal : false,
        safari_warning: false,
        oninit: "quicktagsHide"

FlickrPhotos Version 0.84

Added a new plugin to MT.

Flickr photos is a plugin that enables you to display thumbnail links of Flickr photos in your Movable Type blog. Flickr Photos focuses on displaying lists of photos, in many possible ways.

A Friend asked how to convert multi-row to one row that have multi-column with delimiter in DB2. Such as:

c1
----
a
b
c

==>

c2
----------
a | b | c

Actually, There are 2 different requirements on this point. The first one is that the multi-row number is limited and could be referred as a flag. Such as:

c1        c2
----      ----
10        Jan
21         Feb
88         Mar
...         ...
04         Dec

The second one is that the multi-row number is unlimited or can not be referred with any character.

There's the way that uses pure SQL to implement the first requirement. Here's a sample: changing row to column

select
emp_id,
sum(case month(pay_date)
 when 1 then salary
 else 0
end) Jan,
sum(case month(pay_date)
 when 2 then salary
 else 0
end) Feb,
sum(case month(pay_date)
 when 3 then salary
 else 0
end) Mar,
sum(case month(pay_date)
 when 4 then salary
 else 0
end) Apr,
sum(case month(pay_date)
 when 5 then salary
 else 0
end) May,
sum(case month(pay_date)
 when 6 then salary
 else 0
end) Jun,
sum(case month(pay_date)
 when 7 then salary
 else 0
end) Jul,
sum(case month(pay_date)
 when 8 then salary
 else 0
end) Aug,
sum(case month(pay_date)
 when 9 then salary
 else 0
end) Sep,
sum(case month(pay_date)
 when 10 then salary
 else 0
end) Oct,
sum(case month(pay_date)
 when 11 then salary
 else 0
end) Nov,
sum(case month(pay_date)
 when 12 then salary
 else 0
end) Dec,
sum(salary) total
from emp_salary
group by emp_id 

To the 2nd requirement, the only way is to write use-defined function for it. It accepts a cursor and output one row. Here's a sample in Oracle.

SELECT 
a.deptno, 
a.dname, 
a.loc, rowtocol('SELECT DISTINCT job 
FROM emp WHERE deptno = ' ||a.deptno) as jobs 
FROM dept a;

CREATE OR REPLACE FUNCTION rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' ) 
RETURN VARCHAR2 AUTHID CURRENT_USER AS ...    
TYPE c_refcur IS REF CURSOR;
lc_str VARCHAR2(4000);     
lc_colval VARCHAR2(4000);     
c_dummy c_refcur;     
l number;     
BEGIN
OPEN c_dummy FOR p_slct;     
LOOP
FETCH c_dummy INTO lc_colval;     
EXIT WHEN c_dummy%NOTFOUND;     
lc_str := lc_str || p_dlmtr || lc_colval;     
END LOOP;     
CLOSE c_dummy;     
RETURN SUBSTR(lc_str,2);   
/*      
EXCEPTION      
WHEN OTHERS THEN     
lc_str := SQLERRM;     
IF c_dummy%ISOPEN THEN     
CLOSE c_dummy;     
END IF;     
RETURN lc_str;     
*/
END;

Update 2008-08-01 

A little update on the topic.

What if when the function SUM() is not allowed for the data type? See below example:

with tmp(c1,c2,c3) as (
values (1,'a',1), (1,'b',2)
)
select * from tmp
@

C1          C2 C3         
----------- -- -----------
1 a            1
1 b            2
2 record(s) selected.

The answer is we could use MAX(), MIN() like below:

select 
c1, 
max(case c3 when 1 then c2 end), 
max(case c3 when 2 then c2 end) 
from tmp group by c1@ 

 C1          2 3
----------- - -
1 a b
1 record(s) selected.