--------------------------------------------------------------------------------
-- Database
--------------------------------------------------------------------------------

-- DROP DATABASE livez;
CREATE DATABASE livez
	WITH OWNER = postgres
	ENCODING = 'UNICODE'
	TABLESPACE = pg_default;

--------------------------------------------------------------------------------

COMMENT ON DATABASE livez IS 'Live Z データベース';


--------------------------------------------------------------------------------
-- i-mode
--------------------------------------------------------------------------------

-- DROP TABLE imode_series CASCADE;
CREATE TABLE imode_series (
	series_name VARCHAR(10) NOT NULL,
	family      SMALLINT    NOT NULL,
	foma        BOOLEAN     NOT NULL DEFAULT FALSE,
	CONSTRAINT pk_imode_series PRIMARY KEY (series_name)
);

COMMENT ON TABLE imode_series IS 'iモードシリーズマスタ';
COMMENT ON COLUMN imode_series.series_name IS 'シリーズ名(略称)';
COMMENT ON COLUMN imode_series.family IS '系統';
COMMENT ON COLUMN imode_series.foma IS 'FOMA';
COMMENT ON CONSTRAINT pk_imode_series ON imode_series IS '主キー';



-- DROP TABLE imode_html_version CASCADE;
CREATE TABLE imode_html_version (
	html FLOAT NOT NULL,
	CONSTRAINT pk_imode_html_version PRIMARY KEY (html),
	CONSTRAINT chk_html$imode_html_version CHECK (html >= 1.0)
);

COMMENT ON TABLE imode_html_version IS 'i-HTMLバージョンマスタ';
COMMENT ON COLUMN imode_html_version.html IS 'i-HTMLバージョン';
COMMENT ON CONSTRAINT pk_imode_html_version ON imode_html_version IS '主キー';



-- DROP TABLE imode_xhtml_version CASCADE;
CREATE TABLE imode_xhtml_version (
	xhtml FLOAT NOT NULL,
	CONSTRAINT pk_imode_xhtml_version PRIMARY KEY (xhtml),
	CONSTRAINT chk_xhtml$imode_xhtml_version CHECK (xhtml >= 1.0)
);

COMMENT ON TABLE imode_xhtml_version IS 'i-XHTMLバージョンマスタ';
COMMENT ON COLUMN imode_xhtml_version.xhtml IS 'i-XHTMLバージョン';
COMMENT ON CONSTRAINT pk_imode_xhtml_version ON imode_xhtml_version IS '主キー';



-- DROP TABLE imode_color_version CASCADE;
CREATE TABLE imode_color_version (
	color INTEGER NOT NULL,
	CONSTRAINT pk_imode_color_version PRIMARY KEY (color),
	CONSTRAINT chk_color$imode_color_version CHECK (color > 0)
);

COMMENT ON TABLE imode_color_version IS 'iモード端末色数/階調数マスタ';
COMMENT ON COLUMN imode_color_version.color IS '色数/階調数';
COMMENT ON CONSTRAINT pk_imode_color_version ON imode_color_version IS '主キー';



-- DROP TABLE imode_imotion_version CASCADE;
CREATE TABLE imode_imotion_version (
	imotion CHAR(3) NOT NULL,
	CONSTRAINT pk_imode_imotion_version PRIMARY KEY (imotion)
);

COMMENT ON TABLE imode_imotion_version IS 'iモーション種別マスタ';
COMMENT ON COLUMN imode_imotion_version.imotion IS 'iモーション種別';
COMMENT ON CONSTRAINT pk_imode_imotion_version ON imode_imotion_version IS '主キー';



-- DROP TABLE imode_barcode_version CASCADE;
CREATE TABLE imode_barcode_version (
	barcode FLOAT NOT NULL,
	CONSTRAINT pk_imode_barcode_version PRIMARY KEY (barcode),
	CONSTRAINT chk_barcode$imode_barcode_version CHECK (barcode >= 1.0)
);

COMMENT ON TABLE imode_barcode_version IS 'iモードバーコードバージョンマスタ';
COMMENT ON COLUMN imode_barcode_version.barcode IS 'バーコードバージョン';
COMMENT ON CONSTRAINT pk_imode_barcode_version ON imode_barcode_version IS '主キー';



-- DROP SEQUENCE seq_id$imode_ssl_version CASCADE;
CREATE SEQUENCE seq_id$imode_ssl_version CYCLE;
-- DROP TABLE imode_ssl_version CASCADE;
CREATE TABLE imode_ssl_version (
	id          INTEGER NOT NULL DEFAULT nextval('seq_id$imode_ssl_version'),
	description TEXT    NOT NULL,
	CONSTRAINT pk_imode_ssl_version PRIMARY KEY (id),
	CONSTRAINT unq_description$imode_ssl_version UNIQUE (description)
);

COMMENT ON TABLE imode_ssl_version IS 'iモードSSLマスタ';
COMMENT ON COLUMN imode_ssl_version.id IS 'ID';
COMMENT ON COLUMN imode_ssl_version.description IS '説明';
COMMENT ON CONSTRAINT pk_imode_ssl_version ON imode_ssl_version IS '主キー';



-- DROP TABLE imode CASCADE;
CREATE TABLE imode (
	name           VARCHAR(10) NOT NULL,
	series_name    VARCHAR(10) NOT NULL,
	product_name   VARCHAR(20)          DEFAULT NULL,
	html           FLOAT       NOT NULL,
	xhtml          FLOAT                DEFAULT NULL,

	browser_column SMALLINT             DEFAULT NULL,
	browser_row    SMALLINT             DEFAULT NULL,
	browser_width  SMALLINT    NOT NULL,
	browser_height SMALLINT    NOT NULL,
	screen_width   SMALLINT             DEFAULT NULL,
	screen_height  SMALLINT             DEFAULT NULL,
	monochrome     BOOLEAN     NOT NULL DEFAULT FALSE,
	color          INTEGER     NOT NULL,
	jpeg           BOOLEAN     NOT NULL DEFAULT FALSE,

	imotion        CHAR(3)              DEFAULT NULL,
	pdf            BOOLEAN     NOT NULL DEFAULT FALSE,
	chara_den      BOOLEAN     NOT NULL DEFAULT FALSE,
	toruca         BOOLEAN     NOT NULL DEFAULT FALSE,
	ichannel       BOOLEAN     NOT NULL DEFAULT FALSE,
	menu_icon      BOOLEAN     NOT NULL DEFAULT FALSE,
	barcode        FLOAT                DEFAULT NULL,
	ssl_type       INTEGER              DEFAULT NULL,

	CONSTRAINT pk_imode PRIMARY KEY (name),

	CONSTRAINT fk_series_name$imode FOREIGN KEY (series_name)
		REFERENCES imode_series (series_name)
		ON UPDATE CASCADE,

	CONSTRAINT unq_product_name$imode UNIQUE (product_name),

	CONSTRAINT fk_html$imode FOREIGN KEY (html)
		REFERENCES imode_html_version (html)
		ON UPDATE CASCADE,
	CONSTRAINT fk_xhtml$imode FOREIGN KEY (xhtml)
		REFERENCES imode_xhtml_version (xhtml)
		ON UPDATE CASCADE,

	CONSTRAINT chk_browser_column$imode CHECK (browser_column > 0 OR browser_column IS NULL),
	CONSTRAINT chk_browser_row$imode CHECK (browser_row > 0 OR browser_row IS NULL),
	CONSTRAINT chk_browser_width$imode CHECK (browser_width > 0),
	CONSTRAINT chk_browser_height$imode CHECK (browser_height > 0),
	CONSTRAINT chk_screen_width$imode CHECK (screen_width > 0 OR screen_width IS NULL),
	CONSTRAINT chk_screen_height$imode CHECK (screen_height > 0 OR screen_height IS NULL),

	CONSTRAINT fk_color$imode FOREIGN KEY (color)
		REFERENCES imode_color_version (color)
		ON UPDATE CASCADE,
	CONSTRAINT fk_imotion$imode FOREIGN KEY (imotion)
		REFERENCES imode_imotion_version (imotion)
		ON UPDATE CASCADE,
	CONSTRAINT fk_barcode$imode FOREIGN KEY (barcode)
		REFERENCES imode_barcode_version (barcode)
		ON UPDATE CASCADE,
	CONSTRAINT fk_ssl_type$imode FOREIGN KEY (ssl_type)
		REFERENCES imode_ssl_version (id)
		ON UPDATE CASCADE
);

COMMENT ON TABLE imode IS 'iモード端末マスタ';
COMMENT ON COLUMN imode.name IS '機種名';
COMMENT ON COLUMN imode.series_name IS 'シリーズ名';
COMMENT ON COLUMN imode.product_name IS '製品名';
COMMENT ON COLUMN imode.html IS 'i-HTMLバージョン';
COMMENT ON COLUMN imode.xhtml IS 'i-XHTMLバージョン';
COMMENT ON COLUMN imode.browser_column IS 'ブラウザ表示桁数';
COMMENT ON COLUMN imode.browser_row IS 'ブラウザ表示行数';
COMMENT ON COLUMN imode.browser_width IS 'ブラウザ幅(ピクセル)';
COMMENT ON COLUMN imode.browser_height IS 'ブラウザ高さ(ピクセル)';
COMMENT ON COLUMN imode.screen_width IS '待受け幅(ピクセル)';
COMMENT ON COLUMN imode.screen_height IS '待受け高さ(ピクセル)';
COMMENT ON COLUMN imode.monochrome IS '白黒画面';
COMMENT ON COLUMN imode.color IS '色数/階調数';
COMMENT ON COLUMN imode.jpeg IS 'JPEG対応';
COMMENT ON COLUMN imode.imotion IS 'iモーション対応';
COMMENT ON COLUMN imode.pdf IS 'PDF対応';
COMMENT ON COLUMN imode.chara_den IS 'キャラ電対応';
COMMENT ON COLUMN imode.toruca IS 'トルカ対応';
COMMENT ON COLUMN imode.ichannel IS 'iチャネル対応';
COMMENT ON COLUMN imode.menu_icon IS 'メニューアイコン';
COMMENT ON COLUMN imode.barcode IS 'バーコード対応';
COMMENT ON COLUMN imode.ssl_type IS 'SSL対応';
COMMENT ON CONSTRAINT pk_imode ON imode IS '主キー';



-- DROP TABLE imode_subscreen CASCADE;
CREATE TABLE imode_subscreen (
	name   VARCHAR(10) NOT NULL,
	width  SMALLINT    NOT NULL,
	height SMALLINT    NOT NULL,

	CONSTRAINT pk_imode_subscreen PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_subscreen FOREIGN KEY (name)
		REFERENCES imode (name)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT chk_width$imode_subscreen CHECK (width > 0),
	CONSTRAINT chk_height$imode_subscreen CHECK (height > 0)
);

COMMENT ON TABLE imode_subscreen IS 'iモード端末背面画面マスタ';
COMMENT ON COLUMN imode_subscreen.name IS '機種名';
COMMENT ON COLUMN imode_subscreen.width IS '待受け幅(ピクセル)';
COMMENT ON COLUMN imode_subscreen.height IS '待受け高さ(ピクセル)';
COMMENT ON CONSTRAINT pk_imode_subscreen ON imode_subscreen IS '主キー';



-- DROP TABLE imode_doja_version CASCADE;
CREATE TABLE imode_doja_version (
	doja FLOAT NOT NULL,
	CONSTRAINT pk_imode_doja_version PRIMARY KEY (doja),
	CONSTRAINT chk_doja$imode_doja_version CHECK (doja >= 1.0)
);

COMMENT ON TABLE imode_doja_version IS 'iアプリDoJaバージョンマスタ';
COMMENT ON COLUMN imode_doja_version.doja IS 'DoJaバージョン';
COMMENT ON CONSTRAINT pk_imode_doja_version ON imode_doja_version IS '主キー';



-- DROP TABLE imode_jar_version CASCADE;
CREATE TABLE imode_jar_version (
	size SMALLINT NOT NULL,
	CONSTRAINT pk_imode_jar_version PRIMARY KEY (size),
	CONSTRAINT chk_size$imode_jar_version CHECK (size > 0)
);

COMMENT ON TABLE imode_jar_version IS 'iアプリJAR容量マスタ';
COMMENT ON COLUMN imode_jar_version.size IS 'JAR容量(KB)';
COMMENT ON CONSTRAINT pk_imode_jar_version ON imode_jar_version IS '主キー';



-- DROP TABLE imode_sp_version CASCADE;
CREATE TABLE imode_sp_version (
	size SMALLINT NOT NULL,
	CONSTRAINT pk_imode_sp_version PRIMARY KEY (size),
	CONSTRAINT chk_size$imode_sp_version CHECK (size > 0)
);

COMMENT ON TABLE imode_sp_version IS 'iアプリScratchPad容量マスタ';
COMMENT ON COLUMN imode_sp_version.size IS 'ScratchPad容量(KB)';
COMMENT ON CONSTRAINT pk_imode_sp_version ON imode_sp_version IS '主キー';



-- DROP SEQUENCE seq_id$imode_felica_version CASCADE;
CREATE SEQUENCE seq_id$imode_felica_version CYCLE;
-- DROP TABLE imode_felica_version CASCADE;
CREATE TABLE imode_felica_version (
	id          INTEGER      NOT NULL DEFAULT nextval('seq_id$imode_felica_version'),
	description VARCHAR(255) NOT NULL,
	CONSTRAINT pk_imode_felica_version PRIMARY KEY (id),
	CONSTRAINT unq_description$imode_felica_version UNIQUE (description)
);

COMMENT ON TABLE imode_felica_version IS 'iモードFeliCaマスタ';
COMMENT ON COLUMN imode_felica_version.id IS 'ID';
COMMENT ON COLUMN imode_felica_version.description IS '説明';
COMMENT ON CONSTRAINT pk_imode_felica_version ON imode_felica_version IS '主キー';



-- DROP TABLE imode_iappli CASCADE;
CREATE TABLE imode_iappli (
	name    VARCHAR(10) NOT NULL,
	doja    FLOAT       NOT NULL,
	edition BOOLEAN     NOT NULL DEFAULT FALSE,
	jar     SMALLINT    NOT NULL,
	sp      SMALLINT    NOT NULL,
	width   SMALLINT    NOT NULL,
	height  SMALLINT    NOT NULL,
	felica  INTEGER               DEFAULT NULL,

	CONSTRAINT pk_imode_iappli PRIMARY KEY (name),

	CONSTRAINT fk_name$imode_iappli FOREIGN KEY (name)
		REFERENCES imode (name)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT fk_doja$imode_iappli FOREIGN KEY (doja)
		REFERENCES imode_doja_version (doja)
		ON UPDATE CASCADE,
	CONSTRAINT fk_jar$imode_iappli FOREIGN KEY (jar)
		REFERENCES imode_jar_version (size)
		ON UPDATE CASCADE,
	CONSTRAINT fk_sp$imode_iappli FOREIGN KEY (sp)
		REFERENCES imode_sp_version (size)
		ON UPDATE CASCADE,

	CONSTRAINT chk_width$imode_iappli CHECK (width > 0),
	CONSTRAINT chk_height$imode_iappli CHECK (height > 0),

	CONSTRAINT fk_felica$imode_iappli FOREIGN KEY (felica)
		REFERENCES imode_felica_version (id)
		ON UPDATE CASCADE
);

COMMENT ON TABLE imode_iappli IS 'iアプリマスタ';
COMMENT ON COLUMN imode_iappli.name IS '機種名';
COMMENT ON COLUMN imode_iappli.doja IS 'DoJaバージョン';
COMMENT ON COLUMN imode_iappli.edition IS '亜種';
COMMENT ON COLUMN imode_iappli.jar IS 'JAR容量(KB)';
COMMENT ON COLUMN imode_iappli.sp IS 'ScratchPad容量(KB)';
COMMENT ON COLUMN imode_iappli.width IS '描画幅(ピクセル)';
COMMENT ON COLUMN imode_iappli.height IS '描画高さ(ピクセル)';
COMMENT ON COLUMN imode_iappli.felica IS 'FeliCa対応';
COMMENT ON CONSTRAINT pk_imode_iappli ON imode_iappli IS '主キー';



-- DROP TABLE imode_flash_version CASCADE;
CREATE TABLE imode_flash_version (
	version FLOAT NOT NULL,
	CONSTRAINT pk_imode_flash_version PRIMARY KEY (version),
	CONSTRAINT chk_version$imode_flash_version CHECK (version >= 1.0)
);

COMMENT ON TABLE imode_flash_version IS 'iモードFlashバージョンマスタ';
COMMENT ON COLUMN imode_flash_version.version IS 'バージョン';
COMMENT ON CONSTRAINT pk_imode_flash_version ON imode_flash_version IS '主キー';



-- DROP TABLE imode_flash CASCADE;
CREATE TABLE imode_flash (
	name          VARCHAR(10) NOT NULL,
	version       FLOAT       NOT NULL,
	screen_height SMALLINT    NOT NULL,
	subscreen     BOOLEAN     NOT NULL DEFAULT FALSE,

	CONSTRAINT pk_imode_flash PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_flash FOREIGN KEY (name)
		REFERENCES imode (name)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT fk_version$imode_flash FOREIGN KEY (version)
		REFERENCES imode_flash_version (version)
		ON UPDATE CASCADE,
	CONSTRAINT chk_screen_height$imode_flash CHECK (screen_height > 0)
);

COMMENT ON TABLE imode_flash IS 'iモードFlashマスタ';
COMMENT ON COLUMN imode_flash.name IS '機種名';
COMMENT ON COLUMN imode_flash.version IS 'Flash Liteバージョン';
COMMENT ON COLUMN imode_flash.screen_height IS '待受け高さ(ピクセル)';
COMMENT ON COLUMN imode_flash.subscreen IS '背面画面対応';
COMMENT ON CONSTRAINT pk_imode_flash ON imode_flash IS '主キー';



-- DROP TABLE imode_decomail_version CASCADE;
CREATE TABLE imode_decomail_version (
	version FLOAT NOT NULL,
	CONSTRAINT pk_imode_decomail_version PRIMARY KEY (version)
);

COMMENT ON TABLE imode_decomail_version IS 'デコメールバージョンマスタ';
COMMENT ON COLUMN imode_decomail_version.version IS 'デコメールバージョン';
COMMENT ON CONSTRAINT pk_imode_decomail_version ON imode_decomail_version IS '主キー';



-- DROP TABLE imode_decomail CASCADE;
CREATE TABLE imode_decomail (
	name     VARCHAR(10) NOT NULL,
	version  FLOAT       NOT NULL,
	template BOOLEAN     NOT NULL DEFAULT FALSE,
	CONSTRAINT pk_imode_decomail PRIMARY KEY (name),
	CONSTRAINT fk_name$imode_decomail FOREIGN KEY (name)
		REFERENCES imode (name)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT fk_version$imode_decomail FOREIGN KEY (version)
		REFERENCES imode_decomail_version (version)
		ON UPDATE CASCADE ON DELETE CASCADE
);

COMMENT ON TABLE imode_decomail IS 'デコメールマスタ';
COMMENT ON COLUMN imode_decomail.name IS '機種名';
COMMENT ON COLUMN imode_decomail.version IS 'デコメールバージョン';
COMMENT ON COLUMN imode_decomail.template IS 'テンプレート対応';
COMMENT ON CONSTRAINT pk_imode_decomail ON imode_decomail IS '主キー';



-- DROP TABLE imode_frame CASCADE;
CREATE TABLE imode_frame (
	name   VARCHAR(10) NOT NULL,
	width  SMALLINT    NOT NULL,
	height SMALLINT    NOT NULL,

	CONSTRAINT pk_imode_frame PRIMARY KEY (name, width, height),
	CONSTRAINT fk_name$imode_frame FOREIGN KEY (name)
		REFERENCES imode (name)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT chk_width$imode_frame CHECK (width > 0),
	CONSTRAINT chk_height$imode_frame CHECK (height > 0)
);

COMMENT ON TABLE imode_frame IS 'iモード写真フレーム画像マスター';
COMMENT ON COLUMN imode_frame.name IS '機種名';
COMMENT ON COLUMN imode_frame.width IS '幅(ピクセル)';
COMMENT ON COLUMN imode_frame.height IS '高さ(ピクセル)';
COMMENT ON CONSTRAINT pk_imode_frame ON imode_frame IS '主キー';


--------------------------------------------------------------------------------
-- EZweb
--------------------------------------------------------------------------------

-- DROP TABLE ezweb_series CASCADE;
CREATE TABLE ezweb_series (
	name VARCHAR(10) NOT NULL,
	CONSTRAINT pk_ezweb_series PRIMARY KEY (name)
);

COMMENT ON TABLE ezweb_series IS 'EZweb シリーズマスタ';
COMMENT ON COLUMN ezweb_series.name IS 'シリーズ名';
COMMENT ON CONSTRAINT pk_ezweb_series ON ezweb_series IS '主キー';



-- DROP TABLE ezweb CASCADE;
CREATE TABLE ezweb (
	device_type   CHAR(4)     NOT NULL,
	product_name  VARCHAR(20) NOT NULL,
	series_name   VARCHAR(10)          DEFAULT NULL,
	screen_width  SMALLINT             DEFAULT NULL,
	screen_height SMALLINT             DEFAULT NULL,
	gif           BOOLEAN     NOT NULL DEFAULT FALSE,
	jpeg          BOOLEAN     NOT NULL DEFAULT FALSE,
	png_8bit      BOOLEAN     NOT NULL DEFAULT FALSE,
	bmp_2bit      BOOLEAN     NOT NULL DEFAULT FALSE,
	bmp_1bit      BOOLEAN     NOT NULL DEFAULT FALSE,
	draft         BOOLEAN     NOT NULL DEFAULT FALSE,

	CONSTRAINT pk_ezweb PRIMARY KEY (device_type),
	CONSTRAINT unq_product_name$ezweb UNIQUE (device_type, product_name),

	CONSTRAINT fk_series_name$ezweb FOREIGN KEY (series_name)
		REFERENCES ezweb_series (name)
		ON UPDATE CASCADE,

	CONSTRAINT chk_screen_width$ezweb CHECK (screen_width > 0 OR screen_width IS NULL),
	CONSTRAINT chk_screen_height$ezweb CHECK (screen_height > 0 OR screen_height IS NULL)
);

COMMENT ON TABLE ezweb IS 'EZweb 端末マスタ';
COMMENT ON COLUMN ezweb.device_type IS 'デバイスID';
COMMENT ON COLUMN ezweb.product_name IS '機種名称';
COMMENT ON COLUMN ezweb.series_name IS 'シリーズ名';
COMMENT ON COLUMN ezweb.screen_width IS '待受け幅(ピクセル)';
COMMENT ON COLUMN ezweb.screen_height IS '待受け高さ(ピクセル)';
COMMENT ON COLUMN ezweb.gif IS 'GIF対応';
COMMENT ON COLUMN ezweb.jpeg IS 'JPEG対応';
COMMENT ON COLUMN ezweb.png_8bit IS '8bit(256色)PNG対応';
COMMENT ON COLUMN ezweb.bmp_2bit IS '2bit(4階調)BMP対応';
COMMENT ON COLUMN ezweb.bmp_1bit IS '1bit(2階調)BMP対応';
COMMENT ON COLUMN ezweb.draft IS '暫定';
COMMENT ON CONSTRAINT pk_ezweb ON ezweb IS '主キー';



-- DROP TABLE ezweb_frame CASCADE;
CREATE TABLE ezweb_frame (
	device_type CHAR(4)  NOT NULL,
	width       SMALLINT NOT NULL,
	height      SMALLINT NOT NULL,

	CONSTRAINT pk_ezweb_frame PRIMARY KEY (device_type, width, height),
	CONSTRAINT fk_device_type$ezweb_frame FOREIGN KEY (device_type)
		REFERENCES ezweb (device_type)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT chk_width$ezweb_frame CHECK (width > 0),
	CONSTRAINT chk_height$ezweb_frame CHECK (height > 0)
);

COMMENT ON TABLE ezweb_frame IS 'EZweb 写真フレームマスタ';
COMMENT ON COLUMN ezweb_frame.device_type IS 'デバイスID';
COMMENT ON COLUMN ezweb_frame.width IS '幅(ピクセル)';
COMMENT ON COLUMN ezweb_frame.height IS '高さ(ピクセル)';
COMMENT ON CONSTRAINT pk_ezweb_frame ON ezweb_frame IS '主キー';



-- DROP TABLE ezweb_stamp CASCADE;
CREATE TABLE ezweb_stamp (
	device_type CHAR(4)  NOT NULL,
	width       SMALLINT NOT NULL,
	height      SMALLINT NOT NULL,

	CONSTRAINT pk_ezweb_stamp PRIMARY KEY (device_type),
	CONSTRAINT fk_device_type$ezweb_stamp FOREIGN KEY (device_type)
		REFERENCES ezweb (device_type)
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT chk_width$ezweb_stamp CHECK (width > 0),
	CONSTRAINT chk_height$ezweb_stamp CHECK (height > 0)
);

COMMENT ON TABLE ezweb_stamp IS 'EZweb 写真スタンプマスタ';
COMMENT ON COLUMN ezweb_stamp.device_type IS 'デバイスID';
COMMENT ON COLUMN ezweb_stamp.width IS '幅(ピクセル)';
COMMENT ON COLUMN ezweb_stamp.height IS '高さ(ピクセル)';
COMMENT ON CONSTRAINT pk_ezweb_stamp ON ezweb_stamp IS '主キー';


--------------------------------------------------------------------------------
-- Vodafone
--------------------------------------------------------------------------------

-- DROP TABLE vodafone CASCADE;
CREATE TABLE vodafone (
	name         VARCHAR(10) NOT NULL,
	product_name VARCHAR(20)          DEFAULT NULL,
	flash        CHAR(3)              DEFAULT NULL,
	CONSTRAINT pk_vodafone PRIMARY KEY (name),
	CONSTRAINT unq_product_name$vodafone UNIQUE (product_name)
);

COMMENT ON TABLE vodafone IS 'ボーダフォン端末マスター';
COMMENT ON COLUMN vodafone.name IS '機種名';
COMMENT ON COLUMN vodafone.product_name IS '製品名';
COMMENT ON COLUMN vodafone.flash IS 'Flash';
COMMENT ON CONSTRAINT pk_vodafone ON vodafone IS '主キー';
