weblog

技術的なメモ置き場。

閉包テーブルの更新（その１）

SQL

閉包テーブル（Closure Table）の更新について書く。

閉包テーブルについては以下を参照。 lab.kochlein.com

サンプルテーブル

CREATE TABLE folders (
      folder_id   SERIAL PRIMARY KEY
    , name        TEXT NOT NULL
);

CREATE TABLE ctable (
      ancestor    BIGINT NOT NULL -- 先祖
    , descendant  BIGINT NOT NULL -- 子孫
    , PRIMARY KEY (ancestor, descendant)
    , FOREIGN KEY (ancestor) REFERENCES folders(folder_id)
    , FOREIGN KEY (descendant) REFERENCES folders(folder_id)
);

サンプルデータ

構造

テーブル

folder_id name
1 フォルダ1
2 フォルダ2
3 フォルダ3
4 フォルダ4
5 フォルダ5
ancestor descendant
1 1
1 2
1 3
1 4
1 5
2 2
2 3
3 3
4 4
4 5
5 5

子孫の取得

-- フォルダ2の子孫
SELECT f.*
  FROM folders AS f INNER JOIN ctable AS ct
    ON f.folder_id = ct.descendant
 WHERE ct.ancestor = 2;

先祖の取得

-- フォルダ4の先祖
SELECT f.*
  FROM folders AS f INNER JOIN ctable AS ct
    ON f.folder_id = ct.ancestor
 WHERE ct.descendant = 4;

子の追加

-- フォルダ6を追加
INSERT INTO folders VALUES (6, 'フォルダ6');

-- フォルダ5にフォルダ6を追加
INSERT INTO ctable (ancestor, descendant)
-- フォルダ5を子孫として参照する行
SELECT ct.ancestor, 6
  FROM ctable AS ct
 WHERE ct.descendant = 5
UNION ALL
-- 自己参照の行
SELECT 6, 6;

葉ノードの削除

DELETE FROM ctable WHERE descendant = 3;

サブツリーの削除

DELETE 
  FROM ctable
 WHERE descendant IN (SELECT descendant
                        FROM ctable
                       WHERE ancestor = 2);

サブツリーの移動

-- フォルダ4をフォルダ3に移動する
-- 自己参照を除く、先祖と子孫を削除
DELETE 
  FROM ctable
 WHERE descendant IN (SELECT descendant
                        FROM ctable
                       WHERE ancestor = 4)
   AND ancestor   IN (SELECT ancestor
                        FROM ctable
                       WHERE descendant = 4
                         AND ancestor != descendant);

INSERT INTO ctable (ancestor, descendant)
SELECT super.ancestor, sub.descendant
  FROM ctable AS super
 CROSS JOIN ctable AS sub
 WHERE super.descendant = 3
   AND sub.ancestor = 4;

参考

SQLアンチパターン

CentOS7 にPostgreSQL9.5をインストールする

PostgreSQL CentOS

ダウンロード

[vagrant@localhost ~]$ wget http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

インストール

[vagrant@localhost ~]$ sudo rpm -ivh pgdg-centos95-9.5-2.noarch.rpm
[vagrant@localhost ~]$ sudo yum -y install postgresql95-server postgresql95-devel postgresql95-contrib

 

起動

[vagrant@localhost ~]$ sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
[vagrant@localhost ~]$ sudo systemctl start postgresql-9.5

 

システム起動時に自動起動させる

[vagrant@localhost ~]$ sudo systemctl enable postgresql-9.5

 

postgresユーザが追加されていることを確認

[vagrant@localhost ~]$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)

 

postgresユーザに切り替え

[vagrant@localhost ~]$ sudo -i -u postgres

 

psqlの設定

-bash-4.2$ vi .psqlrc


.psqlrcに以下の内容を記載する

\set COMP_KEYWORD_CASE upper
\pset null (null)



COMP_KEYWORD_CASE upper ・・・ SQLキーワードを大文字で補完する
pset null (null)　・・・　nullのカラムに表示する内容

PostgreSQLに接続

-bash-4.2$ psql
拡張表示が自動的に使用されます
Null 表示は "(null)" です。
psql (9.5.3)
"help" でヘルプを表示します.

postgres=#




これで完了。


参考
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7
http://lets.postgresql.jp/documents/tutorial/centos/2
http://postd.cc/postgresql-on-the-command-line/