S2Container+S2Dao.PHP5を使ってみる(その2)

ログイン/会員登録画面は、大体amazonなどのサイトを真似て作ればよいので、必要な画面は大体見えてます。その上で、データベース上に必要なテーブルを適当に作成し、そこからS2Baseの機能を使ってeitityとdaoを生成します。

テーブルは予めeclipse上でclayを使ってざくっとモデリングして、DDLを生成し、MySQL　QUERY Browserとかで実行します。(ERDツールはMYSQL Workbenchというのもあるみたいですがまだ試してない。)

clayのDDL生成ですが、

• Createテーブル
• Createインデックス
• ユニークキーと重複するインデックスは作らない
• スキーマ名を使わない
• ALTER TABLEで外部キーを定義する
• ALTER TABLEでユニークキーを定義する

にチェックを入れて生成します。でないと複数のテーブルをスクリプトで生成しようとする時、キーの制約によって怒られてしまいます。下記の感じで(なんか問題あるかも知れませんがまあとりあえずいいや。)

CREATE TABLE countries (
       id INTEGER NOT NULL AUTO_INCREMENT
     , name VARCHAR(100) NOT NULL
     , short_name VARCHAR(20) NOT NULL
     , entry DATETIME
     , version_no INTEGER
     , timestamp DATETIME NOT NULL
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE INDEX IX_short_name ON countries (short_name ASC);
CREATE INDEX IX_name ON countries (name ASC);

CREATE TABLE prefectures (
       id INTEGER NOT NULL AUTO_INCREMENT
     , name VARCHAR(20) NOT NULL
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE UNIQUE INDEX IX_name ON prefectures (name ASC);

CREATE TABLE delivery_methods (
       id INTEGER NOT NULL AUTO_INCREMENT
     , name VARCHAR(50)
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE INDEX IX_name ON delivery_methods (name ASC);

CREATE TABLE payment_methods (
       id INTEGER NOT NULL AUTO_INCREMENT
     , name VARCHAR(50) NOT NULL
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE INDEX IX_name ON payment_methods (name ASC);

CREATE TABLE members (
       id INTEGER NOT NULL AUTO_INCREMENT
     , code VARCHAR(20)
     , first_name VARCHAR(100) NOT NULL
     , last_name VARCHAR(100) NOT NULL
     , first_name_kana VARCHAR(100) NOT NULL
     , last_name_kana VARCHAR(100) NOT NULL
     , phone VARCHAR(20) NOT NULL
     , cell_phone VARCHAR(20)
     , email VARCHAR(255) NOT NULL
     , cell_phone_email VARCHAR(255)
     , sex BOOL NOT NULL
     , date_of_birth DATE
     , password VARCHAR(20) NOT NULL
     , point INTEGER NOT NULL DEFAULT 0
     , remainder_q VARCHAR(100)
     , remainder_a VARCHAR(100)
     , default_address_id INTEGER
     , resident_address_id INTEGER
     , default_delivery_method_id INTEGER
     , default_payment_method_id INTEGER
     , is_deleted BOOL
     , last_login DATETIME
     , entry DATETIME NOT NULL
     , version_no INTEGER
     , timestamp TIMESTAMP NOT NULL
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE INDEX IX_code ON members (code ASC);
CREATE INDEX IX_first_name ON members (first_name ASC);
CREATE INDEX IX_last_name ON members (last_name ASC);
CREATE INDEX IX_first_name_kana ON members (first_name_kana ASC);
CREATE INDEX IX_last_name_kana ON members (last_name_kana ASC);
CREATE INDEX IX_date_of_birth ON members (date_of_birth ASC);
CREATE INDEX IX_id_deleted ON members (is_deleted ASC);
CREATE INDEX IX_last_login ON members (last_login ASC);
CREATE INDEX IX_entry ON members (entry ASC);

CREATE TABLE addresses (
       id INTEGER NOT NULL AUTO_INCREMENT
     , member_id INTEGER NOT NULL
     , country_id INTEGER NOT NULL
     , zip_code INTEGER NOT NULL
     , prefecture_id INTEGER NOT NULL
     , city VARCHAR(255) NOT NULL
     , street_address VARCHAR(255) NOT NULL
     , building_name VARCHAR(255) NOT NULL
     , phone VARCHAR(20)
     , fax VARCHAR(20)
     , is_deleted BOOL
     , entry DATETIME NOT NULL
     , version_no INTEGER
     , timestamp TIMESTAMP NOT NULL
     , PRIMARY KEY (id)
)TYPE=InnoDB;
CREATE INDEX IX_zip ON addresses (zip_code ASC);
CREATE INDEX IX_id_deleted ON addresses (is_deleted ASC);

FKは省いてますが、作ってる途中で制約のせいで挿入やら削除ができないとムカつくため。ほんとは下記の様にしたいかも。

ALTER TABLE members
  ADD CONSTRAINT FK_default_delivery_mothod_id
      FOREIGN KEY (default_delivery_method_id)
      REFERENCES delivery_methods (id);

ALTER TABLE members
  ADD CONSTRAINT FK_default_payment_method_id
      FOREIGN KEY (default_payment_method_id)
      REFERENCES payment_methods (id);

ALTER TABLE addresses
  ADD CONSTRAINT FK_prefecture_id
      FOREIGN KEY (prefecture_id)
      REFERENCES prefectures (id);

ALTER TABLE addresses
  ADD CONSTRAINT FK_country_id
      FOREIGN KEY (country_id)
      REFERENCES countries (id);

ALTER TABLE addresses
  ADD CONSTRAINT FK_member_id
      FOREIGN KEY (member_id)
      REFERENCES members (id)
   ON DELETE SET NULL
   ON UPDATE NO ACTION;

さてこれでテーブルが出来たので、S2Baseの出番です。まず
LoginSample\build.xml
をチェックして、dbname、user、password等の項目を適当に設定します。

    <target name="gen-dao" depends="prepare">
        <property name="dsn" value="mysql:host=localhost; dbname=test" />
        <property name="user" value="root" />
        <property name="password" value="test" />
        <gdao toDir="app/commons/dao" />
    </target>

LoginSampleのフォルダで、phingを実行します。

phing dao-gen

これで
LoginSample\app\commons\dao
に、データベース内の各テーブルに対応したdao、entityが出来ました。ここで出来るdaoやentityも、より都合のよいものが出来るように、あらかじめ
LoginSample\lib\S2Dao\src\phing\task\skel
以下のファイルや
LoginSample\lib\S2Dao\src\phing\task\S2DaoSkeletonTask.php
を弄っておきます。こんな感じになるようにしてます。

<?php
/**
 * @author 
 * @since 2006/06/23
 */
interface AddresseDao
{
	const BEAN = "AddresseEntity";
	
	public function update(AddresseEntity $entity);
	public function insert(AddresseEntity $entity);
	public function delete(AddresseEntity $entity);

	public function findAllArray();
	public function getAddresseByIdList($id);

}

?>

<?php

/**
 * @author 
 * @since 2006/06/23
 */
class AddresseEntity implements Serializable
{
	const TABLE = "addresses";
	
	const id_COLUMN = "id";
	const memberId_COLUMN = "member_id";
	const countryId_COLUMN = "country_id";
	const zipCode_COLUMN = "zip_code";
	const prefectureId_COLUMN = "prefecture_id";
	const city_COLUMN = "city";
	const streetAddress_COLUMN = "street_address";
	const buildingName_COLUMN = "building_name";
	const phone_COLUMN = "phone";
	const fax_COLUMN = "fax";
	const isDeleted_COLUMN = "is_deleted";
	const entry_COLUMN = "entry";
	const versionNo_COLUMN = "version_no";
	const timestamp_COLUMN = "timestamp";

	private $id;
	private $memberId;
	private $countryId;
	private $zipCode;
	private $prefectureId;
	private $city;
	private $streetAddress;
	private $buildingName;
	private $phone;
	private $fax;
	private $isDeleted;
	private $entry;
	private $versionNo;
	private $timestamp;

	public function getId(){return $this->id;}
	public function setId($id){$this->id = $id;}
	public function getMemberId(){return $this->memberId;}
	public function setMemberId($memberId){$this->memberId = $memberId;}
	public function getCountryId(){return $this->countryId;}
	public function setCountryId($countryId){$this->countryId = $countryId;}
	public function getZipCode(){return $this->zipCode;}
	public function setZipCode($zipCode){$this->zipCode = $zipCode;}
	public function getPrefectureId(){return $this->prefectureId;}
	public function setPrefectureId($prefectureId){$this->prefectureId = $prefectureId;}
	public function getCity(){return $this->city;}
	public function setCity($city){$this->city = $city;}
	public function getStreetAddress(){return $this->streetAddress;}
	public function setStreetAddress($streetAddress){$this->streetAddress = $streetAddress;}
	public function getBuildingName(){return $this->buildingName;}
	public function setBuildingName($buildingName){$this->buildingName = $buildingName;}
	public function getPhone(){return $this->phone;}
	public function setPhone($phone){$this->phone = $phone;}
	public function getFax(){return $this->fax;}
	public function setFax($fax){$this->fax = $fax;}
	public function getIsDeleted(){return $this->isDeleted;}
	public function setIsDeleted($isDeleted){$this->isDeleted = $isDeleted;}
	public function getEntry(){return $this->entry;}
	public function setEntry($entry){$this->entry = $entry;}
	public function getVersionNo(){return $this->versionNo;}
	public function setVersionNo($versionNo){$this->versionNo = $versionNo;}
	public function getTimestamp(){return $this->timestamp;}
	public function setTimestamp($timestamp){$this->timestamp = $timestamp;}

	public function toString()
	{
		$buf = "";
		$buf .= "id = " . $this->id . ", ";
		$buf .= "memberId = " . $this->memberId . ", ";
		$buf .= "countryId = " . $this->countryId . ", ";
		$buf .= "zipCode = " . $this->zipCode . ", ";
		$buf .= "prefectureId = " . $this->prefectureId . ", ";
		$buf .= "city = " . $this->city . ", ";
		$buf .= "streetAddress = " . $this->streetAddress . ", ";
		$buf .= "buildingName = " . $this->buildingName . ", ";
		$buf .= "phone = " . $this->phone . ", ";
		$buf .= "fax = " . $this->fax . ", ";
		$buf .= "isDeleted = " . $this->isDeleted . ", ";
		$buf .= "entry = " . $this->entry . ", ";
		$buf .= "versionNo = " . $this->versionNo . ", ";
		$buf .= "timestamp = " . $this->timestamp . " {";
		$buf .= "}";
		return $buf;
	}

	public function serialize(){
		$prop = array();
		foreach($this as $key => $value){
			$prop[$key] = $value;
		}
		return serialize($prop);
	}

	public function unserialize($serialized)
	{
		foreach(unserialize($serialized) as $key => $value)
		{
			$this->$key = $value;
		}
	}
	
	public function hashCode() {
		return $this->getId();
	}
}
?>

ついでにserializeやらtoStringやら実装した状態で出来上がるようにしてある。

続く...