Posts Tagged Propel

MySQL: Dari Desain Ke Kode Lebih Mudah

MySQL sebagai RDBMS open source, sangat mudah dijumpai pada server-server hosting atau di kalangan akademisi maupun korporat. Oracle sebagai pemilik MySQL juga mengembangkan aplikasi pendukung MySQL Workbench (dahulu MySQL GUI Tools). Fungsi utama MySQL Workbench adalah:

  1. Desain database (modelling).
  2. Administrasi Server.
  3. SQL Frontend untuk Database MySQL.

MySQL Workbench sendiri dikembangkan secara open source, juga dapat diperluas fungsi-fungsinya dengan modul dan plugin yang dapat ditulis menggunakan bahasa pemrograman Python ataupun Lua.

Bagi pengembang yang ingin memanfaatkan fungsi-fungsi database MySQL dalam aplikasi mereka, tentunya akan sangat dimudahkan dengan fungsi desain model database yang terdapat pada MySQL Workbench. Dengan beragamnya bahasa pemrograman aplikasi yang dipakai, entah itu pemrograman web server dengan PHP, web server berbasis Javascript (Nodejs) ataupun framework-framework lainnya.

Kami, dari kalangan komunitas open source mengembangkan aplikasi mandiri yang dapat membaca file model aplikasi MySQL Workbench untuk diekspor ke dalam bahasa pemrograman dan framework lain.

Adalah MySQL Workbench Schema Exporter yang dapat mengekspor ke dalam:

  1. Doctrine ORM.
  2. Propel ORM.
  3. Zend Framework.
  4. Sencha ExtJS.
  5. Node Sequelize.

Kode program MySQL Workbench Schema Exporter dapat diambil dari https://github.com/johmue/mysql-workbench-schema-exporter.

, , , , , , , ,

Leave a comment

Extending Propel in Symfony 1.4

Allow Table name prefixed with underscore

Replace the original symfony/lib/plugins/sfPropelPlugin/lib/addon/sfPropelDatabaseSchema.class.php:

  public function getChildren($hash)
  {
    foreach ($hash as $key => $value)
    {
      // ignore special children (starting with _)
      if ($key[0] == '_')
      {
        unset($hash[$key]);
      }
    }

    return $hash;
  }

with:

  public function getChildren($hash)
  {
    foreach ($hash as $key => $value)
    {
      // ignore special children (starting with _)
      if (in_array($key, array('_attributes', '_behaviors', '_propel_behaviors', '_inheritance', '_nestedSet', '_foreignKeys', '_indexes', '_uniques')))
      {
        unset($hash[$key]);
      }
    }

    return $hash;
  }

Now you can define a table name which is prefixed by underscore in your config/schema.yml:

connection:                      propel
defaultIdMethod:                 native
package:                         lib.model

classes:
  MyTable:
    tableName:                   _my_table
    columns:
      id:
      column1:                   { type: varchar, size: 30 }
      column2:                   { type: varchar, size: 255 }

Match the Propel Generated Model Files with Symfony Coding Standard

Propel 1.4 introduce behaviors named alternative_coding_standarts which can be used to alter Propel generated model files according to symfony. But, this behavior known not working and need to be pacthed. Locate symfony/lib/plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/behavior/AlternativeCodingStandardsBehavior.php, and change:

		if($this->getParameter('brackets_newline') == 'true') {
			$filter['#^(/t*)/}/h(else|elseif|catch)(.*)/h/{$#m'] = "$1}
$1$2$3
$1{";
			$filter['#^(/t*)(/w.*)/h/{$#m'] = "$1$2
$1{";
		}

to:

		if($this->getParameter('brackets_newline') == 'true') {
			// class
			$filter['#^(\w.*)\h\{#m'] = "$1\n{";
			// line ending with {
			$filter['#^(\t+)(\w.*)\h\{#m'] = "$1$2\n$1{";
			// } something {
			$filter['#^(\t+)\}\h(else|elseif|catch)(.*)\h\{#m'] = "$1}\n$1$2$3\n$1{";
		}

Change the config/propel.ini to include alternative_coding_standarts behavior as default:

propel.behavior.default                        = symfony,symfony_i18n

to:

propel.behavior.default                        = symfony,symfony_i18n,alternative_coding_standards

And then rebuild your model files.

Adding Behavior to Add __toString() Method for Model Files

To be able to automatically generate __toString() method, symfony_tostring behavior need to be added as default behavior or registered as symfony behaviors in config/schema.yml.

propel.behavior.default                        = symfony,symfony_i18n,symfony_tostring,alternative_coding_standards
propel.behavior.symfony_tostring.class         = plugins.sfPropelPlugin.lib.behavior.SfPropelBehaviorToString

To include a column in __toString() method, define an attribute toString: true to column definition, as in:

classes:
  MyTable:
    tableName:                   _my_table
    columns:
      id:
      column1:                   { type: varchar, size: 30, toString: true }
      column2:                   { type: varchar, size: 255 }

This behavior accept an argument named separator which will be used to concatenate the column if there are many columns defined with toString attribute.

classes:
  MyTable:
    tableName:                   _my_table
    columns:
      id:
      column1:                   { type: varchar, size: 30, toString: true }
      column2:                   { type: varchar, size: 255, toString: true }
    behaviors:
      symfony_tostring:
        separator:               ' - '

Dont forget to rebuild your models.

Download: http://numpang.bkdjombang.com/files/symfony/1.4/extending-propel-in-symfony-1.4.zip

Leave a comment

MySQL Workbench Plugin to Export Schema as Symfony Propel Schema

MySQL Workbench provides DBAs and developers an integrated tools environment for:

  • Database Design & Modeling
  • SQL Development (replacing MySQL Query Browser)
  • Database Administration (replacing MySQL Administrator)

There are numerous MySQL Workbench plugin to export the MySQL schema into Propel, one is found here. This plugin export the schema into Propel xml schema.

For a symfony users, in the Wiki, has a plugin which based on the previous one.

My work extends the symfony one, with added features:

  • Export as Symfony Propel New Schema
  • Allow to sort the result by the tablename
  • Integrated changes from version 0.5 of PropelExport.grt.lua
  • Added support for YEAR field type
  • New 1.05: Added mapping for BLOB, MEDIUMBLOB, and LONGBLOB column
  • New 1.05: Fixed handling of multi columns foreign keys

Download:
Github: https://github.com/tohenk/mysql-workbench-propel-schema
Version 1.05 (Dec 17, 2010): symfony_propel_schema.grt.lua
Version 1.04: symfony_propel_schema_export.grt.lua

, ,

3 Comments

Propel 1.3 Blob Field Workarround on Symfony

VARBINARY or LONGVARBINARY column should be mapped as blob column. But in Propel 1.3 they are mapped wrong. The setter for the column will not work when the column is saved, the column is treated as string instead of resource. The column value will be something like Resource id #nn. See this ticket.

You can apply this patch to symfony to fix this issue.

, ,

Leave a comment

Adding Access Logging to Symfony Application

sfAccessLoggerPlugin introduces a way of logging your site visits. To accomplish this logging, the others plugin is needed:

  1. sfGuardPlugin for User management.
  2. sfPropelUuidBehaviorPlugin for generating Universally Unique Identifier (UUID).
  3. sfRemoteIPPlugin, an extract of function found in sfPropelAuditPlugin by Sacha Telgenhof Oude Koehorst to detect the remote IP Address.
  4. sfBrowscapPlugin, a simple symfony wrapper for phpbrowscap to determine the client browser capability.
  5. The sfAccessLoggerPlugin itself.

Configuration:

  1. Instal all plugins and enable them.
  2. Do propel build model, forms, filters, and SQL, then insert generated SQL to your database.
  3. In your backend application, enable bundled module sfAccessLogViewer to provide admin style generated module to view the access log.

, , , , ,

Leave a comment

Using Multiple Primary Keys in Admin Generator

Sometimes, the table we’re using force us to use multiple primary keys as shown in the schema below (config/schema.yml):

connection:            propel
defaultIdMethod:       native
package:               lib.model

classes:
  State:
    tableName:         state
    columns:
      id:              { type: varchar, size: 2, primaryKey: true }
      country_id:      { type: varchar, size: 2, primaryKey: true }
      name:            { type: varchar, size: 50, index: true }
      created_at:

By default, the symfony Admin Generator will only support single primary key. Since symfony 1.2, which adds sfRoute class, using multiple primary keys is possible by a configuration modifications and adds a few lines of code.

So now, generate the admin module by issuing:

symfony propel:generate-admin frontend State

Look at the generated route in apps/frontend/config/routing.yml:

state:
  class: sfPropelRouteCollection
  options:
    model:                State
    module:               state
    prefix_path:          state
    column:               id
    with_wildcard_routes: true

Only a single primary key was generated, so the idea is concatenate the primary keys as a single column, separated by a dash (-) for example.

Modify the route:

state:
  class: sfPropelRouteCollection
  options:
    model:                State
    module:               state
    prefix_path:          state
    column:               countryandstate
    with_wildcard_routes: true
    model_methods:
      object:             doSelectForRoute
  requirements:           { countryandstate: '[a-zA-Z0-9\.\_\-\:]+' }

As seen in the modified route, we change the column name to countryandstate, add a model-methods for object configuration and change the countryandstate column requirements.

When an object converted as a route, the sfRoute class will check for toParams() method, so we can provide the value of countryandstate column by adding this code:

// lib/model/State.php
<?php

class State extends BaseState
{
  public function toParams()
  {
    return array('countryandstate' => implode('-', array($this->getCountryId(), $this->getId())));
  }
}

A model-methods for object configuration in the routing above, changes the method used by the sfRoute class to retrieve an object which match a route.

// lib/model/StatePeer.php
<?php

class StatePeer extends BaseStatePeer
{
  /**
   * Retrieve State object for routing.
   *
   * @param array $parameters  The route parameters
   * @return State
   */
  public static function doSelectForRoute($parameters)
  {
    if (!isset($parameters['countryandstate']))
    {
      return null;
    }

    $c = new Criteria();
    list($country, $state) = explode('-', $parameters['countryandstate']);
    $c->add(StatePeer::COUNTRY_ID, $country)
      ->add(StatePeer::ID, $state);

    return StatePeer::doSelectOne($c);
  }
}

The rest is, to customize the generated module and adjusting the form as you need.

, , ,

13 Comments