2017年11月 – ページ 2 – ラズパイ(linux)メモ

今回から本番のアプリを作成していきます。
顧客情報と対応履歴を管理できるようにします。

まずデータベースを作成します。
電話システムとも連動させる予定なので、
Customer Relationship Management With Phone Systemということで
データベース名をcrmpとして作成します。

環境構築の参考にして下さい。
create dtabase crmp

テーブルを作成します。

users	ユーザアカウント管理	ログイン名やパスワードを登録します。
clients	顧客情報	住所や電話番号など、顧客情報を登録します。
telnos	電話番号	顧客情報以外の複数の電話番号がある場合（担当者の携帯電話など）、登録します。顧客情報と関連しています。
records	対応履歴	対応履歴を登録します。顧客情報と関連しています。
record_statuses	対応状態	対応履歴の種別（クレーム対応や問合せなど）を管理します。対応履歴と関連しています。

テーブル・カラムの命名規約はこちらなどを参照にしてください。
下記sql文を実行して作成。

CREATE TABLE customers (
    id integer NOT NULL,
    name character varying(50) NOT NULL,
    kana character varying(50),
    address character varying(255),
    telno character varying(25),
    fax character varying(25),
    mail character varying(50),
    memo text,
    created timestamp without time zone,
    modified timestamp with time zone
);
ALTER TABLE customers OWNER TO cakeuser;
CREATE SEQUENCE customers_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE customers_id_seq OWNER TO cakeuser;
ALTER SEQUENCE customers_id_seq OWNED BY customers.id;

CREATE TABLE record_statuses (
    id integer NOT NULL,
    priority integer,
    name character varying(25) NOT NULL,
    memo text,
    created timestamp with time zone,
    modified timestamp with time zone
);
ALTER TABLE record_statuses OWNER TO cakeuser;
CREATE SEQUENCE record_statuses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE record_statuses_id_seq OWNER TO cakeuser;
ALTER SEQUENCE record_statuses_id_seq OWNED BY record_statuses.id;

CREATE TABLE records (
    id integer NOT NULL,
    record_date timestamp with time zone DEFAULT now(),
    title character varying(255),
    customer_id integer,
    memo text,
    created timestamp with time zone,
    modified timestamp with time zone,
    record_status_id integer
);
ALTER TABLE records OWNER TO cakeuser;
CREATE SEQUENCE records_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE records_id_seq OWNER TO cakeuser;
ALTER SEQUENCE records_id_seq OWNED BY records.id;

CREATE TABLE telnos (
    id integer NOT NULL,
    name character varying(50),
    telno character varying(25),
    customer_id integer,
    memo text,
    created timestamp with time zone,
    modified timestamp with time zone
);
ALTER TABLE telnos OWNER TO cakeuser;
CREATE SEQUENCE telnos_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE telnos_id_seq OWNER TO cakeuser;
ALTER SEQUENCE telnos_id_seq OWNED BY telnos.id;

CREATE TABLE users (
    id integer NOT NULL,
    username character varying(50) NOT NULL,
    password character varying(255) NOT NULL,
    lastname character varying(255),
    firstname character varying(255),
    role character varying(20),
    created timestamp without time zone,
    modified timestamp without time zone
);
ALTER TABLE users OWNER TO cakeuser;
CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE users_id_seq OWNER TO cakeuser;
ALTER SEQUENCE users_id_seq OWNED BY users.id;

ALTER TABLE ONLY customers ALTER COLUMN id SET DEFAULT nextval('customers_id_seq'::regclass);
ALTER TABLE ONLY record_statuses ALTER COLUMN id SET DEFAULT nextval('record_statuses_id_seq'::regclass);
ALTER TABLE ONLY records ALTER COLUMN id SET DEFAULT nextval('records_id_seq'::regclass);
ALTER TABLE ONLY telnos ALTER COLUMN id SET DEFAULT nextval('telnos_id_seq'::regclass);
ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);

ALTER TABLE ONLY customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (id);
ALTER TABLE ONLY record_statuses
    ADD CONSTRAINT record_statuses_pkey PRIMARY KEY (id);
ALTER TABLE ONLY records
    ADD CONSTRAINT records_pkey PRIMARY KEY (id);
ALTER TABLE ONLY telnos
    ADD CONSTRAINT telnos_pkey PRIMARY KEY (id);
ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

CREATE TABLE customers (

id integer NOT NULL,

name character varying(50) NOT NULL,

kana character varying(50),

address character varying(255),

telno character varying(25),

fax character varying(25),

mail character varying(50),

memo text,

created timestamp without time zone,

modified timestamp with time zone

);

ALTER TABLE customers OWNER TO cakeuser;

CREATE SEQUENCE customers_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE customers_id_seq OWNER TO cakeuser;

ALTER SEQUENCE customers_id_seq OWNED BY customers.id;

CREATE TABLE record_statuses (

id integer NOT NULL,

priority integer,

name character varying(25) NOT NULL,

memo text,

created timestamp with time zone,

modified timestamp with time zone

);

ALTER TABLE record_statuses OWNER TO cakeuser;

CREATE SEQUENCE record_statuses_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE record_statuses_id_seq OWNER TO cakeuser;

ALTER SEQUENCE record_statuses_id_seq OWNED BY record_statuses.id;

CREATE TABLE records (

id integer NOT NULL,

record_date timestamp with time zone DEFAULT now(),

title character varying(255),

customer_id integer,

memo text,

created timestamp with time zone,

modified timestamp with time zone,

record_status_id integer

);

ALTER TABLE records OWNER TO cakeuser;

CREATE SEQUENCE records_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE records_id_seq OWNER TO cakeuser;

ALTER SEQUENCE records_id_seq OWNED BY records.id;

CREATE TABLE telnos (

id integer NOT NULL,

name character varying(50),

telno character varying(25),

customer_id integer,

memo text,

created timestamp with time zone,

modified timestamp with time zone

);

ALTER TABLE telnos OWNER TO cakeuser;

CREATE SEQUENCE telnos_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE telnos_id_seq OWNER TO cakeuser;

ALTER SEQUENCE telnos_id_seq OWNED BY telnos.id;

CREATE TABLE users (

id integer NOT NULL,

username character varying(50) NOT NULL,

password character varying(255) NOT NULL,

lastname character varying(255),

firstname character varying(255),

role character varying(20),

created timestamp without time zone,

modified timestamp without time zone

);

ALTER TABLE users OWNER TO cakeuser;

CREATE SEQUENCE users_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE users_id_seq OWNER TO cakeuser;

ALTER SEQUENCE users_id_seq OWNED BY users.id;

ALTER TABLE ONLY customers ALTER COLUMN id SET DEFAULT nextval('customers_id_seq'::regclass);

ALTER TABLE ONLY record_statuses ALTER COLUMN id SET DEFAULT nextval('record_statuses_id_seq'::regclass);

ALTER TABLE ONLY records ALTER COLUMN id SET DEFAULT nextval('records_id_seq'::regclass);

ALTER TABLE ONLY telnos ALTER COLUMN id SET DEFAULT nextval('telnos_id_seq'::regclass);

ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);

ALTER TABLE ONLY customers

ADD CONSTRAINT customers_pkey PRIMARY KEY (id);

ALTER TABLE ONLY record_statuses

ADD CONSTRAINT record_statuses_pkey PRIMARY KEY (id);

ALTER TABLE ONLY records

ADD CONSTRAINT records_pkey PRIMARY KEY (id);

ALTER TABLE ONLY telnos

ADD CONSTRAINT telnos_pkey PRIMARY KEY (id);

ALTER TABLE ONLY users

ADD CONSTRAINT users_pkey PRIMARY KEY (id);

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO PUBLIC;

<広告（注）以下の書籍は、このページの記載内容とは関係ありません。>

PostgreSQL徹底入門第3版 [ 笠原辰仁 ]

価格:3,542円
(2017/11/24 16:27時点)

PostgreSQL: Up and RunningA Practical Guide to the Advanced Open Source Database【電子書籍】[ Regina O. Obe ]

価格:3,024円
(2017/11/24 16:26時点)

月別アーカイブ: 2017年11月

cakephp3のタイムゾーンの設定

cakephp3 キャッシュのクリア

顧客管理アプリの作成１　データベースの作成