Convert Feedly.opml to MonetDB database

這只是用來測試我最近寫的 tclmonetdb extension。

首先是讀出資料，然後寫入 MonetDB database 的 mylist table：

#!/usr/bin/tclsh
package require tdom
package require monetdb

monetdb db -host localhost -port 50000 -user monetdb -passwd monetdb -dbname demo

# MonetDB does not support drop table if exists syntax, use the alternative method
set stmt [db query "select name from tables where name = 'mylist'"]
set count [$stmt get_row_count]
$stmt close
if {$count > 0} {
    set stmt [db query "drop table mylist"]
    $stmt close
}

set stmt [db query {create table mylist (title TEXT, xmlUrl TEXT, \
          htmlUrl TEXT, PRIMARY KEY (title))}]
$stmt close

# setup feedly opml file path
set filename "c:/feedly.opml"
# parse XML data
set doc [dom parse [tDOM::xmlReadFile $filename "UTF-8"]]
set root [$doc documentElement]
set nodeList [$root selectNodes /opml/body/outline/outline]

set title ""
set xmlUrl ""
set htmlUrl ""

foreach node $nodeList { 
    if {[catch {set title [$node getAttribute title]}] != 0} {
        set title ""
    }
    
    if {[catch {set xmlUrl [$node getAttribute xmlUrl]}] != 0} {
        set xmlUrl ""
    }
    
    if {[catch {set htmlUrl [$node getAttribute htmlUrl]}] != 0} {
        set htmlUrl ""
    }    
    
    set sql_string "insert into mylist values('$title', '$xmlUrl', '$htmlUrl')"    
    set stmt [db query $sql_string]
    $stmt close
}

db close

再來是讀出來，確定我們有正確寫入：

#!/usr/bin/tclsh
package require monetdb

monetdb db -host localhost -port 50000 -user monetdb -passwd monetdb -dbname demo

set stmt [db query {SELECT * FROM mylist}]

set count [$stmt get_row_count]
for {set i 0} { $i < $count} {incr i 1} {
    set mydict [$stmt fetch_row_dict]
    puts "RSS title: [dict get $mydict title]"
    puts "RSS xmlUrl:  [dict get $mydict xmlurl]"
    puts "RSS htmlUrl:  [dict get $mydict htmlurl]"    
}

$stmt close
db close

在 tclmonetdb v0.6 版 redesign 了 API，所以更新這裡的 code。