まぁ、モカでも片手に。:DB

カテゴリ：DB

• PostgreSQLのエラーログ出力
  [ 2011-05-17 15:42 ]
• PostgreSQLでselect結果をファイルに出力する
  [ 2010-12-16 15:23 ]
• PostgreSQLの初歩コマンド集
  [ 2010-12-16 14:53 ]
• mysqldumpでテーブルデータをダンプする
  [ 2008-02-21 19:05 ]
• [SQLite x PHP] PHPからSQLite・insert文を実行する
  [ 2007-02-21 13:39 ]
• [SQLite] テーブル作成とデータのインポート／エクスポート、バキューム
  [ 2007-02-08 17:31 ]
• [SQLite] DB作成・起動・終了・table/index/db表示
  [ 2007-02-07 18:09 ]
• PostgreSQLの最初の一杯
  [ 2004-02-18 10:14 ]

PostgreSQLのエラーログ出力

postgresql.conf　により設定。自分の環境では /usr/local/pgsql/data/postgresql.conf にありました。

log_destination = 'stderr'

となっていたので、標準出力にログが出力されていた模様。　で

logging_collector = off
→　サーバ起動時のみ設定可能。
　　stderr ・CSV書式のログ出力に送られるメッセージをログファイルにリダイレクトするか否か。
　　syslogへのログより優れている点：syslogでは出力されない種類のメッセージもログに取れる。
　　例）ダイナミックリンカのエラーメッセージ
　　on の場合は、　log_directory =　でログのディレクトリを指定する

…だったので、要はログはとってなかったらしい orz

PostgreSQLでselect結果をファイルに出力する

コマンドラインから出力　（-A 桁そろえをしない、列区切りはタブ）
$ psql db_name -c 'SELECT id FROM users;' -A -F \t

psqlから出力

全レコードを出力 (列区切りはタブ）
　psql# COPY table_name TO '/home/tmp/data.txt' DELIMITERS '\t';

　※COPYコマンドはimportにも使えるよう。
　　COPY table_name FROM ''/home/tmp/data.csv' DELIMITER ','

select文結果を出力
psql# \o /home/tmp/data.txt　　　　…出力先をファイルに
psql# \a　　　　　　　　　　　　　　　　　　　　…桁そろえをしない
Output format is unaligned.
psql# \pset fieldsep '\t'　　　　　　　　…セパレータの設定。列区切りはタブ
Field separator is ' '.
psql# SELECT id FROM users;
psql# \o 　　　　　　　　　　　　　　　　　　　…出力先を標準出力に
psql#


※　出力結果から改行を取り除く
上記COPYコマンドを利用すると、改行は\r\n等に置き換えて出力される。
SELECT文の場合は改行のまま出力されるが、それだとその後の処理が面倒だったので
文章が入力されるカラムで改行を空白に置き換えた上で、文字数を100字に切って出力した。

psql# SELECT id, name, substr(regexp_replace(note, '\(\r\n|\r|\n\)', ' ', 'g'), 0, 100) FROM users;

ちなみに、日本語文字が半バイトで切られることはありませんでした(EUC-JP)。

pg_dumpというのもあるので、そのうち使ってみたい。参考

PostgreSQLの初歩コマンド集

以前ちょっと調べるも、結局やらなかったPostgreSQL。最近使い始めました。

psql … コマンドラインツール
・起動
$ psql (データベース名 (-U ユーザー名 -W))

・psqlの内部スラッシュコマンド（メタコマンド、バックスラッシュコマンド）
\c DB名　DBに新たに接続
\l　データベース一覧の表示
\dt　テーブル一覧の表示
\d テーブル名　テーブル構造の表示
\di テーブル名　インデックス一覧の表示
\q　psqlを終了
\?　内部スラッシュコマンドを終了

ユーザ一覧の表示
psql# select usename from pg_user

mysqldumpでテーブルデータをダンプする

わかってないこと多いですが、取りあえずメモ


●文字化けする場合
mysql画面で、文字コード関連の設定を見ると

> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

こうバラバラだから、内部で文字コードが変換されてる？
というわけで、
--default-character-set=latin1
と設定してみたら上手くいきました

●オプションの位置
今回設定したオプションは以下のもの
-c, --complete-insert　…　完全な挿入ステートメント（カラム名も指定）を使用する。
→コピー元と先のテーブルのカラムがちょっと違う（先のほうがカラム数が多い）かもなので

-n, --no-create-db
CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; が出力に含まれない。--databases オプションまたは --all-databases オプションを指定した場合は、上記の行が追加される。

-t, --no-create-info
テーブル作成情報（CREATE TABLE ステートメント）を書き込まない。

--default-character-set

これらは、
-u user_name, --user=user_name
サーバとの接続に使用する MySQL ユーザ名。デフォルト値はユーザの Unix ログイン名。

オプションより前に書くとエラー（というかスルー）になる模様

●特定テーブルをdumpする
ダンプしたいテーブル名を、空白で区切って、データベース名のあとに羅列する。

●実際のコマンド
mysqldump -u USERNAME -p -c -n -t --default-character-set=latin1 -h IP_ADDRESS DB_NAME TABLE_NAME1 TABLE_NAME2 TABLE_NAME3 > dump.sql

●ダンプしたデータを復元（実際は同じテーブル構造の別DB=開発DBでした）する
mysql -u USERNAME DB_NAME < dump.sql

[SQLite x PHP] PHPからSQLite・insert文を実行する

イキナリinsert文でトライ

* sample *
$dbfile = "test.db";

if ($db = sqlite_ope n($dbfile, 0666, $sqliteerror)) {
$sql = "INSERT INTO user VALUES ("1, "user1");
sqlite_query($db, $sql);

} else {
die($sqliteerror);
}
?>

注）エキサイトブログの仕様により、sqlite_ope nの文字列、eとnの間にスペースを打ってありますが、実際のスクリプトではスペースは不要です

まずはopen時に　『file is encrypted or is not a database』　っていうエラー。
これは、SQLiteのdbファイルと、PHPにインストールされているSQLiteのverが違ったことが原因でした。
諸事情により、PHPのverがあげられないため、PHP 4.3.10-18　＆　SQLite 2.8.16 という組み合わせで再実行。

と、またエラー『sqlite_query(): attempt to write a readonly database』が。
これは、dbファイルのパーミッションの問題。
sqlite　コマンドプロンプトでdbを作成すると、0644で出来るので、0666に変更する。
これで、上記スクリプト例の、openまでではエラーがなくなった。

次のエラーは『unable to open database file』。
ファイルそのものに書き込み権限を与えても不十分と発覚。
dbファイルの置いてあるディレクトリにも書き込み権限を立てると、上記スクリプトが動きました。

因みに実際には、phpファイルが置いてある、public_html下でのテストでしたが、public_htmlに書き込み権限を立てるのは危険だと思われるので、dbファイルを別ディレクトリに移し、フルパスでファイル箇所を指定しました。

[SQLite] テーブル作成とデータのインポート／エクスポート、バキューム

文字コードについて
SQLite 3.0で利用可能な文字コードは以下の3通り。
・UTF-8
・UTF-16BE (big-endian)
・UTF-16LE (little-endian)

カラム型
カラムのタイプは、その値そのものにより以下のタイプに分けられる。従来のDBのように、カラム型によるものではない。これはmanifest typingと呼ばれる

TEXT
NUMERIC
INTEGER
REAL
NONE

どのようなロジックで、各値がタイプ分けされるかの詳細はドキュメントの　2.1 Determination Of Column Affinity　を参照

なお、SQLite 3.0より前は、データは全てAsciiで格納されていた。

テーブル作成とデータのインポート／エクスポート

●カラム型
カラム型は、上記の型以外にも、通常のvarchar(10)なども使えるようだ
varchar(10)とした場合、入力されるテキスト長は10文字に限られる模様
ここらへん、まだ読んでいる範囲でマニュアルに記述が見つからないので、今後追記の可能性アリ

●CREATE TABLE文の文法：マニュアル参照

●データのインポート
１．ローカルにテキストファイル（当然上記文字コードのもの）を置く
　・カラム数が可変となっているとエラーになるので注意
　・１レコード内データ順は、create table記述時のカラム順で、セパレータで区切ってあること
　例）create table文
　　create table user (
　　　　no INTEGER,
　　　　name TEXT,
　　　　age INTEGER);
　　
　　data.txtの中身
　　1,name,20

２．SQLite起動

３．セパレータの設定
　例）sqlite> .separator \,

４．インポート
　sqlite> .import text_file_name table_name
　例）sqlite> .import data.txt user

インポート終了しても何の表示も出力されません

※.importは、SQLite 3.0より前はCOPYというSQLコマンドで対応しています。
　例）copy user from "user.txt" USING DELIMITERS ",";
　因みに、タブ区切りテキストを指定して、USING DELIMITERS "\t"、次に""内に実際にタブを入力してみたところ、いずれも正常に動作せず。テーブルの1カラム目にデータ1行全てタブも含めてインサートされていました。諦めてタブをカンマに変換して対応（弱い…）

●テキストファイルに記述されたSQL文の実行
　sqlite> .read text_file_name

●データのエクスポート
　sqlite> .output text_file_name

　出力後、標準出力に出力左記を戻す場合：
　sqlite> .output stdout


テーブルのバキューム

　sqlite> vacuum table_name

※ver.3.1からはauto_vacuumも使用可能。これにより大量のデータをdeleteした場合はファイルサイズが小さくなる。但し、auto_vacuumはｄｂファイルのフラグメントを増やす可能性があるほか、部分的に使われているページファイルを統合はしない。

[SQLite] DB作成・起動・終了・table/index/db表示

SQLiteはデータタイプがベーシックなものに限られるなど、機能限定された簡易的RDB。

SQliteの作成
・ダウンロードページからダウンロードし、コンパイルする
・インストールの必要なし
・シェルまたはDOSプロンプトから、下記「起動」コマンドを打つと、指定されたファイル名でカレントディレクトリにDBを作成する
・(unixの場合のみ？).sqlite_historyという隠しファイルが作成され、入力コマンド全てが保存される

SQliteの起動・終了
●起動
$ sqlite file_name
※　file_nameに相当するDBが無い場合は新規作成される

●終了
sqlite> .exit
sqlite> .quit
またはCtrl+D/C

テーブル・インデックス・データベースの表示
●テーブル一覧を表示
sqlite> .tables
→　SELECT name FROM sqlite_master WHERE type='table'
UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table'
ORDER BY name;

●特定テーブルのインデックス一覧を表示
sqlite> .indices table_name

●オリジナルの CREATE TABLE と CREATE INDEX ステートメントを表示
sqlite> .schema [table_name]
→　SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE [ tbl_name LIKE '%s' AND ] type!='meta'
ORDER BY tbl_name, type DESC, name

●現コネクションでオープン済みの全データベースを表示
sqlite> .databases

参考
sqlite SQLite データベースと会話するプログラム

PostgreSQLの最初の一杯

ORACLE, SQLserver, MySQL, MSAccessとやってきましたが(イヤ、ビートリブとかいうのもやったっけ。RDBMSじゃないけど)、とうとうPostgreSQLをやるハメに。まぁ基本は一緒なんだろうけど。
例によって入るそばから忘れるので、これも最初からメモっておくことにしました。

●PostgreSQLについて。
DBMS.PostgreSQLセッションには2種類ある。
- サーバプロセス(postmaster)
DBファイ管理,クライアントからの接続許可,DBとしての動作
- クライアント(frontend) アプリ

・postmasterは複数コネクションの同時処理を可能とする為、各接続に対し新サーバプロセスをフォークする。
・複数DBを処理できる
・サーバとクライアントが異なるサーバ上で動く場合、TCP/IPで通信する

●DBの作成
$ createdb [mydb]
DB名のデフォル卜はユーザ名

作成DB数に制限なし
DB名:アルファベットで始まる63バイト以内

DBのドロップ
$ dropdb mydb

●DBの操作
以下のいずれかを使用して操作する。
- psql(PostgreSQLのターミナル)の利用
- ODBCをサポートするフロントエンドの利用
- 自作アプリ(マニュアルPart IV参照)

psqlの使用
起動
$ psql [mydb]
DB名のデフォル卜はユーザ名

psqlのプロンプトDBス－パーユーザの場合、
mydb=#　
と、それ以外
mydb=>　
と表示される。

特殊コマンド
SELECT version();
SELECT current_date;
SELECT 2 + 2;

インターナルコマンド("\"で始まる)
\h help
\q quit
\? internal commands,

ヘルプは、OSのシェルでman psqlとしても表示される。

●SQL文について
・空白は複数使ってもひとつとみなされる。
・ダッシュ2個("--"):1行コメント。
・""で囲まれた識別子以外は大小文字の区別なし。
・コンスタント文字は''で引用する。

SQL文のロード

１．
\i の利用。指定ファイルからのコマンドを読み込む。
ex)
mydb=> \i basics.sql
-s ：シングル・ステップ・モード
２．
COPYコマンドの利用
ファイルはサーバ側に必要。

○SELECT文
・集合関数をサポート...count, sum, avg (average), max (maximum), min (minimum)など
・サブクエリをサポート ...*1
・LIKEオペレータ：% ...*2 マニュアル9.6参照

*1
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
*2
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%'
GROUP BY city
HAVING max(temp_lo) < 40;

WHEREとHAVINGの違い
WHERE：インプットとなる行を決定する。
HAVING：インプット行が決定されてから識別される。
集合関数を利用しない条件はHAVINGよりもWHEREに書いたほうが効率的。

○DELETE文
以下の分は全カラム・行を削除するので注意！
DELETE FROM tablename;