php-orm-propel-notes

這學期修系上的 網際網路資料庫程式設計，課程主要使用 PHP + MySQL。

期末專題，在 MySQL 資料庫操作上，沒有直接使用 PHP 內建的 PDO、mysqli

而是使用 ORM(Object Relational Mapping) 進行開發，這次使用的 PHP ORM 叫作 Propel。

高職的時候為了完成畢聯會的程式，就曾經使用過 Propel 進行開發。

當初找 PHP ORM 的資料有看到 Doctrine、Propel，當時看到 Propel 官方頁面比較漂亮，所以就選擇了 Propel，不過再重新選擇一次的話，大概會選擇 Doctrine，原因無它，明顯 Doctrine 開發比較活躍，GitHub 上的狀況看起來比較健康。

不過 Propel 算是一套蠻容易上手的 ORM，寫起來也蠻舒服的，考慮學習成本的情況選擇了熟悉的 Propel。

---

這次主要使用 WAMP (Windows / Apache / MySQL / PHP) 開發環境如下

Windows Version: Windows 7 Professional SP1 64-bit
XAMPP Version: 5.6.30
Composer version 1.4.2 2017-05-17 08:17:52

~# pwd
/c/xampp/htdocs/propel_example

~$ vim composer.json

{
	"require": {
		"propel/propel": "~2.0@dev"
	}
}

~$ composer install

~$ mkdir orm && cd orm

Propel 有一個殺手級功能，Propel 可以針對存在的 database 進行 Schema reverse engineering !

Propel init 是 Propel 的初始化工具，會用問答式的方式 產生出需要的配置文件 (當然也可以自己手動撰寫配置文件，有興趣可以自行參考官方的 Document)

~$ ../vendor/propel/propel/bin/propel init

Propel 2 Initializer

First we need to set up your database connection.

Please pick your favorite database management system:
	[mysql] MySQL
	[sqlite] SQLite
	[pgsql] PostgreSQL
	[oracle] Oracle
	[sqlsrv] MSSQL (via pdo-sqlsrv)
	[mssql] MSSQL (via pdo-mssql)

	> mysql

Please enter your database host [localhost]:
Please enter your database port [3306]:
Please enter your database name: propel_example
Please enter your database user [root]:
Please enter your database password: *password*

Which charset would you like to use? [utf8]:

Connected to sql server successful!

The initial step in every Propel project is the "build". During build time, a developer describes the structure of the datamodel in a XML file called the "schema".

From this schema, Propel generates PHP classes, called "model classes", made of object-oriented PHP code optimized for a given RDMBS. The model classes are the primary interface to find and manipulate data in the database in Propel.

The XML schema can also be used to generate SQL code to setup your database. Alternatively, you can generate the schema from an existing database.

Do you have an existing database you want to use with propel? [no]: yes

Where do you want to store your schema.xml? [C:\xampp\htdocs\propel_example\orm]:
Where do you want propel to save the generated php models? [C:\xampp\htdocs\propel_example\orm]: C:\xampp\htdocs\propel_example\orm\model
Which namespace should the generated php models use?:

Schema reverse engineering finished.

Propel asks you to define some data to work properly, for instance: connection parameters, working directories, flags to take decisions and so on. You can pass these data via a configuration file.

The name of the configuration file is propel, with one of the supported extensions (yml, xml, json, ini, php). E.g. propel.yml or propel.json.

Please enter the format to use for the generated configuration file (yml, xml, json, ini, php) [yml]:
	[0] yml
	[1] xml
	[2] json
	[3] ini
	[4] php

Propel 2 Initializer - Summary

The Propel 2 Initializer will set up your project with the following settings:

Path to schema.xml: C:\xampp\htdocs\propel_example\orm/schema.xml
Path to config file: C:\xampp\htdocs\propel_example\orm/propel.yml
Path to generated php models: C:\xampp\htdocs\propel_example\orm\model
Namespace of generated php models:

Database management system: mysql
Charset: utf8
User: root

Is everything correct? [no]: yes

+ C:\xampp\htdocs\propel_example\orm/schema.xml
+ C:\xampp\htdocs\propel_example\orm/propel.yml
+ C:\xampp\htdocs\propel_example\orm/propel.yml.dist
Successfully wrote PHP configuration in file "C:\xampp\htdocs\propel_example\orm/generated-conf\config.php".

Propel 2 is ready to be used!

下面是從建立用於示範的 propel_example 資料庫中導出的 .sql 文件

--
-- Database: `propel_example`
--

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

--
-- Table structure for table `blog`
--

CREATE TABLE `blog` (
	`id` int(11) NOT NULL,
	`name` varchar(32) NOT NULL,
	`domain` varchar(256) NOT NULL,
	`owner` varchar(32) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `blog`
--
ALTER TABLE `blog`
	ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `blog`
--
ALTER TABLE `blog`
	MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

這是 Propel 針對 database 進行 reverse engineering 生成的 schema.xml(Database Schema)

<?xml version="1.0" encoding="utf-8"?>
<database name="default" defaultIdMethod="native" defaultPhpNamingMethod="underscore">
	<table name="blog" idMethod="native" phpName="Blog">
		<column name="id" phpName="Id" type="INTEGER" primaryKey="true" autoIncrement="true" required="true"/>
		<column name="name" phpName="Name" type="VARCHAR" size="32" required="true"/>
		<column name="domain" phpName="Domain" type="VARCHAR" size="256" required="true"/>
		<column name="owner" phpName="Owner" type="VARCHAR" size="32" required="true"/>
		<column name="created_at" phpName="CreatedAt" type="TIMESTAMP" required="true" defaultExpr="CURRENT_TIMESTAMP"/>
		<vendor type="mysql">
			<parameter name="Engine" value="InnoDB"/>
		</vendor>
	</table>
</database>

~$ vim composer.json
{
	"require": {
		"propel/propel": "~2.0@dev"
	},
	"autoload": {
		"classmap": ["./orm/model"]
	}
}
~$ composer dump-autoload

下面是一個使用 propel model 的範例，簡單的實作 CRUD (Create、Read、Update、Delete)，命名為 crud.php

<?php
	require_once './vendor/autoload.php';
	require_once './orm/generated-conf/config.php';

	// Create
	$blog = new Blog();
	$blog->setName("Ming's blog");
	$blog->setDomain("https://blog.alone.tw/");
	$blog->setOwner("Ming");
	$blog->save();

	// Read
	$blogQuery = new BlogQuery();
	$blogQueryResult = $blogQuery->create()->findOneByName("Ming's blog");

	echo '<pre>' . var_export($blogQueryResult, true) . '</pre>';
	echo "
	id: {$blogQueryResult->getId()} / 
	name: {$blogQueryResult->getName()} / 
	domain: {$blogQueryResult->getDomain()} /
	owner: {$blogQueryResult->getOwner()}
	";

	// Update
	$blogQuery = new BlogQuery();
	$blogQueryResult = $blogQuery->create()->findOneByName("Ming's blog");

	$blogQueryResult->setName("Ming's dream");
	$blogQueryResult->save();

	// Read (check for updates)
	$blogQuery = new BlogQuery();
	$blogQueryResult = $blogQuery->create()->findOneById(1);

	echo '<pre>' . var_export($blogQueryResult, true) . '</pre>';

	// Delete
	$blogQuery = new BlogQuery();
	$blogQueryResult = $blogQuery->create()->findOneById(1);
	$blogQueryResult->delete();

	// Read (check for delete)
	$blogQueryResult = $blogQuery->create()->findOneById(1);
	echo '<pre>' . var_export($blogQueryResult, true) . '</pre>';

?>

CRUD 的 output:

// Read
Blog::__set_state(array(
   'new' => false,
   'deleted' => false,
   'modifiedColumns' => 
  array (
  ),
   'virtualColumns' => 
  array (
  ),
   'id' => 1,
   'name' => 'Ming\'s blog',
   'domain' => 'https://blog.alone.tw/',
   'owner' => 'Ming',
   'created_at' => 
  DateTime::__set_state(array(
     'date' => '2017-07-10 23:01:56.000000',
     'timezone_type' => 3,
     'timezone' => 'Europe/Berlin',
  )),
   'alreadyInSave' => false,
))

// Read (check for updates)
Blog::__set_state(array(
   'new' => false,
   'deleted' => false,
   'modifiedColumns' => 
  array (
  ),
   'virtualColumns' => 
  array (
  ),
   'id' => 1,
   'name' => 'Ming\'s dream',
   'domain' => 'https://blog.alone.tw/',
   'owner' => 'Ming',
   'created_at' => 
  DateTime::__set_state(array(
     'date' => '2017-07-10 23:01:56.000000',
     'timezone_type' => 3,
     'timezone' => 'Europe/Berlin',
  )),
   'alreadyInSave' => false,
))

// Read (check for delete)
NULL

下面是取出 Product 這個 table 所有資料的範例

$products = (new ProductQuery())->create()->find();

$jsonArr = array('data' => []);
foreach ($products as $product) {
    array_push($jsonArr['data'], array(
        $product->getId(),
        urlencode($product->getName()),
        urlencode($product->getModel()),
        $product->getPrice(),
        $product->getType()
    ));
}

header('Content-Type: application/json; charset=utf-8');
echo urldecode(json_encode($jsonArr));

順帶一提 model/Base 中的 Blog.php / BlogQuery.php 是很好的 documentation

  /**
   * The value for the name field.
   *
   * @var        string
   */
  protected $name;

/**
   * Get the [name] column value.
   *
   * @return string
   */
  public function getName()
  {
      return $this->name;
  }

  /**
   * Set the value of [name] column.
   *
   * @param string $v new value
   * @return $this|\Blog The current object (for fluent API support)
   */
  public function setName($v)
  {
      if ($v !== null) {
          $v = (string) $v;
      }

      if ($this->name !== $v) {
          $this->name = $v;
          $this->modifiedColumns[BlogTableMap::COL_NAME] = true;
      }

      return $this;
  } // setName()

* @method     ChildBlog findOneById(int $id) Return the first ChildBlog filtered by the id column
* @method     ChildBlog findOneByName(string $name) Return the first ChildBlog filtered by the name column
* @method     ChildBlog findOneByDomain(string $domain) Return the first ChildBlog filtered by the domain column
* @method     ChildBlog findOneByOwner(string $owner) Return the first ChildBlog filtered by the owner column
* @method     ChildBlog findOneByCreatedAt(string $created_at) Return the first ChildBlog filtered by the created_at column *

~$ propel sql:build
~$ propel model:build
~$ propel config:convert
~$ propel sql:insert

Reference

• Installing Propel
• The Build Time
• Basic C.R.U.D. Operations