DB定義作成（コマンドライン）
 参考URL：http://www.dbonline.jp/mysql/database/
-------------------------------------------------
--全データ削除
--truncate grp;truncate grpusr;truncate inf;truncate org;truncate usr;truncate inf;
-------------------------------------------------
CREATE DATABASE ammdb;
CREATE USER libgdc IDENTIFIED BY 'libgdcmanager';
ユーザ一覧確認
    mysql> select User,Host from mysql.user;
--------------------------------------------------

CREATE TABLE ammdb.usr
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,

    usrid integer unique,

    loginid character varying(512),
    name character varying(512),
    kind integer,
    orgid integer,
    orgrole integer,
    stoplogin integer,
    fromdate integer,
    todate integer,
    pwhash character varying(512),
    cres character varying(512),
    cresdate bigint,
    joindate bigint,
    lastlogintrydate bigint,
    loginerrorcount integer,
    lastlogindate bigint,
    modpwdate bigint,
    leavedate bigint,
    status integer,
    CONSTRAINT usr_pkey PRIMARY KEY (id )
);

CREATE TABLE ammdb.org
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,
    
    orgid integer unique,
    
    kind integer,
    name character varying(512),
    adminname character varying(512),
    adminnamekana character varying(512),
    postnum character varying(32),
    address character varying(1024),
    telnum character varying(32),
    mail character varying(512),
    fromdate integer,
    todate integer,
    limitnum integer,
    entrydate bigint,
    approvaldate bigint,
    rejectdate bigint,
    note character varying(5000),
    joindate bigint,
    status integer,
    CONSTRAINT org_pkey PRIMARY KEY (id )
);


CREATE TABLE ammdb.pwtckt
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,
    
    pwtcktid integer unique,
    
    usrid integer,
    ticket character varying(16),
    createdate bigint,
    ticketdate bigint,
    completedate bigint,
    canceldate bigint,
    status integer,
    CONSTRAINT pwtckt_pkey PRIMARY KEY (id )
);


CREATE TABLE ammdb.inf
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,
    
    infid integer unique,
    
    title character varying(512),
    body character varying(5000),
    pubdate bigint,
    status integer,
    CONSTRAINT inf_pkey PRIMARY KEY (id )
);

CREATE TABLE ammdb.grp
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,
    
    grpid  integer unique,
    
    name character varying(512),
    adminusrid integer,
    createdate bigint,
    
    CONSTRAINT grp_pkey PRIMARY KEY (id )
)
;
CREATE TABLE ammdb.grpusr
(
    id serial NOT NULL,
    updatedate bigint,
    updateusrid integer,
    deleted integer,
    
    grpusrid  integer unique,
    
    grpid integer,
    usrid integer,
    invitationdate bigint,
    joindate bigint,
    leavedate bigint,
    admindate bigint,
    statusadmin integer,
    status integer,
    CONSTRAINT grpusr_pkey PRIMARY KEY (id )
);

-- テーブルへ権限を付与
GRANT ALL PRIVILEGES ON ammdb.usr TO libgdc;
GRANT ALL PRIVILEGES ON ammdb.org TO libgdc;
GRANT ALL PRIVILEGES ON ammdb.pwtckt TO libgdc;
GRANT ALL PRIVILEGES ON ammdb.inf TO libgdc;
GRANT ALL PRIVILEGES ON ammdb.grp TO libgdc;
GRANT ALL PRIVILEGES ON ammdb.grpusr TO libgdc;



＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
PostgreSQL検証時のSQL

/*
CREATE TABLE ammdb.usr
(
    id serial NOT NULL,
    updatedate bigint,
    deleted integer,
    usrid serial NOT NULL,
    loginid character varying(512),
    name character varying(512),
    kind integer,
    orgid integer,
    orgrole integer,
    stoplogin integer,
    fromdate integer,
    todate integer,
    pwhash character varying(512),
    cres character varying(512),
    cresdate bigint,
    joindate bigint,
    lastlogintrydate bigint,
    loginerrorcount integer,
    lastlogindate bigint,
    modpwdate bigint,
    leavedate bigint,
    status integer,
    CONSTRAINT usr_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.usr OWNER TO libgdc;
*/
/*
CREATE TABLE ammdb.org
(
    id serial NOT NULL,
    updatedate bigint,
    deleted integer,
    orgid   serial NOT NULL,
    kind integer,
    name character varying(512),
    adminname character varying(512),
    adminnamekana character varying(512),
    postnum character varying(32),
    address character varying(1024),
    telnum character varying(32),
    mail character varying(512),
    fromdate integer,
    todate integer,
    limitnum integer,
    entrydate bigint,
    approvaldate bigint,
    rejectdate bigint,
    note character varying(5000),
    joindate bigint,
    status integer,
    CONSTRAINT org_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.org OWNER TO libgdc;

*/
/*
CREATE TABLE ammdb.pwtckt
(
    id serial NOT NULL,
    updatedate bigint,
    deleted integer,
    usrid integer,
    ticket character varying(16),
    createdate bigint,
    ticketdate bigint,
    completedate bigint,
    canceldate bigint,
    status integer,
    CONSTRAINT pwtckt_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.pwtckt OWNER TO libgdc;

*/
/*
CREATE TABLE ammdb.inf
(
    id serial NOT NULL,
    updatedate bigint,
    deleted integer,
    infid serial NOT NULL,
    title character varying(512),
    body character varying(5000),
    pubdate bigint,
    status integer,
    CONSTRAINT inf_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.inf OWNER TO libgdc;

*/
/*
 *  データベースのテーブル定義 
CREATE TABLE ammdb.grp
(
    id serial NOT NULL,
    updatedate bigint,
    modusrid integer,
    moddate bigint,
    
    grpid serial NOT NULL,
    name character varying(512),
    adminusrid integer,
    createdate bigint,
    
    CONSTRAINT grp_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.grp OWNER TO libgdc;

*/
/*
 *  データベースのテーブル定義 
CREATE TABLE ammdb.grpusr
(
    id serial NOT NULL,
    updatedate bigint,
    modusrid integer,
    moddate bigint,
    
    grpusrid serial NOT NULL,
    grpid integer,
    usrid integer,
    invitationdate bigint,
    joindate bigint,
    leavedate bigint,
    admindate bigint,
    
    statusadmin integer,
    status integer,
    
    CONSTRAINT grpusr_pkey PRIMARY KEY (id )
) WITH ( OIDS=FALSE );
ALTER TABLE ammdb.grpusr OWNER TO libgdc;

*/
