Apache Rivet 提供了 DIO 套件存取資料庫。而 DIO::Postgresql 是以
Pgtcl 作為底層的套件。
我先在 PostgreSQL 建立一個表格 Notes 作為測試：
create table if not exists Notes (id uuid DEFAULT md5(random()::text || clock_timestamp()::text)::uuid,
title varchar(255), body text, created timestamp, PRIMARY KEY (id));
接下來撰寫一個簡單的
程式測試 Apache Rivet。
建立檔案 tdbcnoteservices.tcl，將資料庫的實作部份放在這裡（使用 tdbc::postgres）：
package require tdbc::postgres
proc getAllNotes {} {
set rows {}
tdbc::postgres::connection create db -user danilo -password danilo -port 5432
set stmt [db prepare {select * from Notes order by created}]
$stmt execute
set rows [$stmt allrows -as lists]
$stmt close
db close
return $rows
}
proc getNote {id} {
set myparams [dict create id $id]
tdbc::postgres::connection create db -user danilo -password danilo -port 5432
set stmt [db prepare {select * from Notes where id = :id}]
$stmt execute $myparams
set rows [$stmt allrows -as lists]
$stmt close
db close
return $rows
}
proc addNote {title body} {
set myparams [dict create title $title body $body]
tdbc::postgres::connection create db -user danilo -password danilo -port 5432
set stmt [db prepare {INSERT INTO Notes (Title, Body, Created) values (:title, :body, now())}]
set resultset [$stmt execute $myparams]
set rowcount [$resultset rowcount]
$resultset close
$stmt close
db close
return $rowcount
}
proc updateNote {id title body} {
set myparams [dict create id $id title $title body $body]
tdbc::postgres::connection create db -user danilo -password danilo -port 5432
set stmt [db prepare {UPDATE Notes SET Title = :title, Body = :body where Id = :id}]
set resultset [$stmt execute $myparams]
set rowcount [$resultset rowcount]
$resultset close
$stmt close
db close
return $rowcount
}
proc deleteNote {id} {
set myparams [dict create id $id]
tdbc::postgres::connection create db -user danilo -password danilo -port 5432
set stmt [db prepare {DELETE FROM Notes where Id = :id}]
set resultset [$stmt execute $myparams]
set rowcount [$resultset rowcount]
$resultset close
$stmt close
db close
return $rowcount
}
再來就是測試我比較不熟悉的 DIO 套件，建立 dionoteservices.tcl 檔案，然後實作同樣的功能：
package require DIO
proc getAllNotes {} {
set rows {}
set db [::DIO::handle Postgresql -host localhost -port 5432 -user danilo -pass danilo -db danilo]
set rows [list]
$db forall {select id, title, body, created from Notes order by created} row {
set myrow [list $row(id) $row(title) $row(body) $row(created)]
lappend rows $myrow
}
$db destroy
return $rows
}
proc getNote {id} {
set rows {}
set db [::DIO::handle Postgresql -host localhost -port 5432 -user danilo -pass danilo -db danilo]
set query "select id, title, body, created from Notes where id = '$id'"
set rows [list]
$db forall $query row {
set myrow [list $row(id) $row(title) $row(body) $row(created)]
lappend rows $myrow
}
$db destroy
return $rows
}
proc addNote {title body} {
set db [::DIO::handle Postgresql -host localhost -port 5432 -user danilo -pass danilo -db danilo]
set arrayVar(Title) $title
set arrayVar(Body) $body
set arrayVar(Created) "now()"
set rowcount [$db insert Notes arrayVar]
$db destroy
return $rowcount
}
proc updateNote {id title body} {
set db [::DIO::handle Postgresql -host localhost -port 5432 -user danilo -pass danilo -db danilo]
set arrayVar(Id) $id
set arrayVar(Title) $title
set arrayVar(Body) $body
set rowcount [$db update arrayVar -table Notes -keyfield Id]
$db destroy
return $rowcount
}
proc deleteNote {id} {
set db [::DIO::handle Postgresql -host localhost -port 5432 -user danilo -pass danilo -db danilo]
set rowcount [$db delete $id -table Notes -keyfield Id]
$db destroy
return $rowcount
}
這樣只要 source 的檔案不同，就可以測試不同的資料庫實作部份。
