Gephiを使ってグラフ化画像を生成する(2)

今回はpostgreSQLとDephiライブラリーを連携させようと試みる。
今日は、そのDBを構築しておく。

必要なテーブルは以下の２テーブル。
①Node用テーブル
②edge_t用テーブル

まずデータベースを作成する。

createdb -W -E UTF-8 buddydb

ついでにデータもいくつか入れておこう。

BEGIN;
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:_
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:_
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: egender_t; Type: TABLE; Schema: public; Owner: postgres; Tablespace:_
--

CREATE TABLE node_t (
    id integer NOT NULL,
    idnode integer,
    label text,
    url text
);

ALTER TABLE public.node_t OWNER TO postgres;
CREATE SEQUENCE node_t_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.node_t_id_seq OWNER TO postgres;
ALTER SEQUENCE node_t_id_seq OWNED BY node_t.id;
SELECT pg_catalog.setval('node_t_id_seq', 1, true);
ALTER TABLE node_t ALTER COLUMN id SET DEFAULT nextval('node_t_id_seq'::regclass);

CREATE TABLE edge_t (
    id integer NOT NULL,
    source integer,
    target integer,
    name text,
    weight integer
);

ALTER TABLE public.edge_t OWNER TO postgres;
CREATE SEQUENCE edge_t_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.edge_t_id_seq OWNER TO postgres;
ALTER SEQUENCE edge_t_id_seq OWNED BY edge_t.id;
SELECT pg_catalog.setval('edge_t_id_seq', 1, true);
ALTER TABLE edge_t ALTER COLUMN id SET DEFAULT nextval('edge_t_id_seq'::regclass);

INSERT INTO  node_t (id, idnode, label, url) VALUES (1, 0, '桑田佳祐', 'http://www.uki2view.jp/ukimedia/icon/joy.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (2, 1, '及川光博', 'http://www.uki2view.jp/ukimedia/icon/sad.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (3, 2, '小栗旬', 'http://www.uki2view.jp/ukimedia/icon/angry.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (4, 3, '浜崎あゆみ', 'http://www.uki2view.jp/ukimedia/icon/smile.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (5, 4, '仲間由紀江', 'http://www.uki2view.jp/ukimedia/icon/joy.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (6, 5, '金城武', 'http://www.uki2view.jp/ukimedia/icon/sad.png');
INSERT INTO  node_t (id, idnode, label, url) VALUES (7, 6, '佐々木望', 'http://www.uki2view.jp/ukimedia/icon/smile.png');

INSERT INTO  edge_t (id, source, target, name, weight) VALUES (1, 0, 1, 'knows', 1); 
INSERT INTO  edge_t (id, source, target, name, weight) VALUES (2, 0, 2, 'knows', 1); 
INSERT INTO  edge_t (id, source, target, name, weight) VALUES (3, 0, 3, 'knows', 1); 
INSERT INTO  edge_t (id, source, target, name, weight) VALUES (4, 0, 4, 'knows', 2); 
INSERT INTO  edge_t (id, source, target, name, weight) VALUES (5, 4, 5, 'knows', 1); 
INSERT INTO  edge_t (id, source, target, name, weight) VALUES (6, 4, 6, 'knows', 1); 

ALTER TABLE ONLY node_t
    ADD CONSTRAINT node_t_pkey PRIMARY KEY (id);

ALTER TABLE ONLY edge_t
    ADD CONSTRAINT edge_t_pkey PRIMARY KEY (id);


CREATE INDEX node_t_id_idx ON node_t USING btree (id);
CREATE INDEX node_t_idnode_idx ON node_t USING btree (idnode);

CREATE INDEX edge_t_id_idx ON edge_t USING btree (id);
CREATE INDEX edge_t_source_idx ON edge_t USING btree (source);
CREATE INDEX edge_t_target_idx ON edge_t USING btree (target);

COMMIT;

つづくー
(参考)
PostgreSQL徹底入門 第3版
PostgreSQL全機能リファレンス (アドバンストリファレンスシリーズ)