1. Create a new i18n module using Gii
2. Edit the protected\config\main.php
3. Create the i18n.sqlite-file
4. Creating two Models using SqliteActiveRecords
5. The defaultController.php
6. The view files
7. Working example
8. Going live and DVCS
9. Discussion

There are scenarios when you work with DVCS (like [Mercurial]( http://mercurial.selenic.com/) or [Git]( http://git-scm.com/)) and CDbMessageSource. To my experience keeping the development database and production database in sync can be very time consuming. Plus, when working in a team, you never know when the database was updated.

In this wiki, I show you an alternative approach to this problem. By putting the [CDbMessageSource] in a separate module, firstly we can have a cleaner logic. By using a sqlite file only for the translation database, secondly version control systems can track changes to this (binaray) file. And thirdly, another advantage comes in by simply copying the file from the live server back to the development server. (I always had a hard time logging on to the live system, only to export the two CDbMessageSource tables and then to import them back to the local db.)

Before continuing please ensure:

• You have created a yii webapp
• You can work with [GiiModule]
• Your webserver supports SQLite
• You have a SQLite Manager (such as the Firefox-Plugin SQLite Manager ready working

Mercurial download ¶

Since this is no real extension and nevertheless I provide a lot of code, feel free to go to my public repository and hg clone https://bitbucket.org/pheadeaux/yii-i18n-sqlite-module-demo (Bitbucket site).

Create a new i18n module using Gii ¶

With [GiiModule] create a new module under protected\modules\i18n which generates:

modules\i18n\I18nModule.php
modules\i18n\components
modules\i18n\controllers\DefaultController.php
modules\i18n\messages
modules\i18n\models
modules\i18n\views\default\index.php
modules\i18n\views\layouts

Delete the folder modules\i18n\messages. Create a new folder modules\i18n\data.

With the following edit modules\i18n\I18nModule.php to have a lookup for the category names to be used.

<?php
class I18nModule extends CWebModule {
// leave the rest untouched …
    /**
     * @return array of allowed category Names
     */
    public static function CategoryList() {
        // modify to match app requirements
        return array(
            'App' => 'App',
            'Error' => 'Error',
            'Hint' => 'Hint',
            'EMail' => 'EMail',
            'Pdf' => 'Pdf'
        );
    }
}

Edit the protected\config\main.php ¶

To actually use a SQLite database separate from the normal db-connection, update the protected\config\main.php. Register the new created module, set up a SQLite connection and tell the CDbMessageSource to use the SQLite-file.

<?php
return array(
    //  ......
    // user language (for Locale)
    'language' => 'de',
    //language for messages and views
    'sourceLanguage' => 'en_us',
    // charset to use
    'charset' => 'utf-8',
    // application modules
    'modules' => array(
        'i18n',
    ),
    //  ......
    // application components
    'components' => array(
        // used for i18n-module
        // uncommented line: extension=php_pdo_sqlite.dll in file php.ini and resart apache to make sure correct driver is loaded
        // for TurnkeyLAMP intall do: apt-get install php5-sqlite
        // make shure to set sqlite-FOLDER to access 755 !
        'i18n_db' => array(
            'class' => 'CDbConnection',
            'connectionString' => 'sqlite:' . dirname(__FILE__) . '/../modules/i18n/data/i18n.sqlite',
        ),
        'i18n' => array(
            'class' => 'CDbMessageSource',
            'connectionID' => 'i18n_db',
            'sourceMessageTable' => 'yii_i18n_source_messages', // will not use Yii Core framework/i18n/CdbMessageSource.php public case sensitive attributes values.
            'translatedMessageTable' => 'yii_i18n_messages', // will not use Yii Core framework/i18n/CdbMessageSource.php public case sensitive attributes values.
        ),
    ),
    // application-level parameters that can be accessed
    // using Yii::app()->params['paramName']
    'params' => array(
        // ......
        'supportedLanguages' => array('de' => 'Deutsch', 'en' => 'English'),
    ),
);

Create the i18n.sqlite-file ¶

Use your favorite SQLite Manager to create a SQLite file under modules\i18n\data\i18n.sqlite. The filename of course is completely arbitrary. But be sure to update the samples given here, if you change this name. Init the db with the following which is adapted from the CDbMessageSource API: ~~~ [sql] CREATE TABLE "yii_i18n_source_messages" (

"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"category" varchar NOT NULL,
"message" text

); CREATE TABLE "yii_i18n_messages" ( "id" INTEGER NOT NULL, "language" varchar NOT NULL, "translation" text, PRIMARY KEY ("id","language") ) ~~~ Note: this code works with SQLite only and all relations have to be handled by the PHP code.

Creating two Models using SqliteActiveRecords ¶

Now we have to create the obligatory model-classes for modules\i18n\models\SourceMessage.php and modules\i18n\models\Message.php. But since we want to use the i18n.sqlite-file, both classes have to use a different database-connection than the rest of the application. Thus, we create a third file called modules\i18n\components\SqliteActiveRecord.php. I did not implement the later all by myself. At the time writing I could not find the correct reference.

To wrap things up, here are the three files:

modules\i18n\components\SqliteActiveRecord.php:

<?php
abstract class SqliteActiveRecord extends CActiveRecord
{
    const BELONGS_TO='CBelongsToRelation';
    const HAS_ONE='CHasOneRelation';
    const HAS_MANY='CHasManyRelation';
    const MANY_MANY='CManyManyRelation';
    const STAT='CStatRelation';

    /**
     * @var CDbConnection the default database connection for all active record classes.
     * By default, this is the 'db' application component.
     * @see getDbConnection
     */
    public static $db;

    private static $_models=array();            // class name => model

    private $_md;                               // meta data
    private $_new=false;                        // whether this instance is new or not
    private $_attributes=array();               // attribute name => attribute value
    private $_related=array();                  // attribute name => related objects
    private $_c;                                // query criteria (used by finder only)
    private $_pk;                               // old primary key value

    /**
     * Returns the database connection used by active record.
     * By default, the "db" application component is used as the database connection.
     * You may override this method if you want to use a different database connection.
     * @return CDbConnection the database connection used by active record.
     */
    public function getDbConnection()
    {
        if(self::$db!==null)
            return self::$db;
        else
        {

            // Create CDbConnection and set properties
            self::$db = new CDbConnection();
            foreach(Yii::app()->i18n_db as $key => $value)
                self::$db->$key = $value;

            if(self::$db instanceof CDbConnection)
            {
                self::$db->setActive(true);
                return self::$db;
            }
            else
                throw new CDbException(Yii::t('yii','Active Record requires a "db" CDbConnection application component.'));
        }
    }
}
?>

The two snippets following are merely the same as created by Gii.

modules\i18n\models \SourceMessage.php:

<?php
/**
 * This is the model class for table "yii_i18n_source_messages".
 *
 * The followings are the available columns in table 'yii_i18n_source_messages':
 * @property integer $id
 * @property string $category
 * @property string $message
 *
 * The followings are the available model relations:
 * @property I18nMessages[] $i18nMessages
 */
class SourceMessage extends SqliteActiveRecord {

    /**
     * Returns the static model of the specified AR class.
     * @param string $className active record class name.
     * @return SourceMessages the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'yii_i18n_source_messages';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('category', 'length', 'max' => 32),
            array('category', 'filter', 'filter' => 'trim'),
            array('message', 'required'),
            array('message', 'filter', 'filter' => array($obj = new CHtmlPurifier(), 'purify')),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('id, category, message', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'Messages' => array(self::HAS_MANY, 'Message', 'id'),
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'category' => Yii::t('App','Translation Category'),
            'message' => Yii::t('App','Message'),
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search() {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $sort = new CSort();
        $sort->attributes = array(
            'id',
            'category',
            'message',
        );

        $sort->defaultOrder = 'category, message ';

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('category', $this->category, true);
        $criteria->compare('message', $this->message, true);

        $criteria->with = array('Messages');

        return new CActiveDataProvider(get_class($this), array(
            'criteria' => $criteria,
            'sort' => $sort,
        ));
    }
    // Getter and Setter
    // -------------------------------------------------------------------------

    /**
     * Get the translation with the current app language
     * @return string
     */
    public function getTranslation() {
        if ($this->Messages) {
            $messages = $this->Messages(array("condition" => "language=\"" . Yii::app()->language . "\""));
            return $messages[0]->translation;
        }
        else
            return '...';
    }
}

modules\i18n\models\Message.php:

<?php
/**
 * This is the model class for table "yii_i18n_messages".
 *
 * The followings are the available columns in table 'yii_i18n_messages':
 * @property integer $id
 * @property string $language
 * @property string $translation
 *
 * The followings are the available model relations:
 * @property I18nSourceMessages $id0
 */
class Message extends SqliteActiveRecord {

    /**
     * Returns the static model of the specified AR class.
     * @param string $className active record class name.
     * @return Messages the static model class
     */
    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    /**
     * @return string the associated database table name
     */
    public function tableName() {
        return 'yii_i18n_messages';
    }

    /**
     * @return array validation rules for model attributes.
     */
    public function rules() {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array('id, language', 'safe'),
            array('translation', 'filter', 'filter' => array($obj = new CHtmlPurifier(), 'purify')),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array('id, language, translation', 'safe', 'on' => 'search'),
        );
    }

    /**
     * @return array relational rules.
     */
    public function relations() {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'SourceMessage' => array(self::BELONGS_TO, 'SourceMessage', 'id'),
        );
    }

    public function primaryKey() {
        return array('id', 'language');
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'id' => 'ID',
            'language' => Yii::t('App', 'Language'),
            'languageName' => Yii::t('App', 'Language'),
            'translation' => Yii::t('App', 'Translation'),
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
     */
    public function search() {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.

        $criteria = new CDbCriteria;

        $criteria->compare('id', $this->id);
        $criteria->compare('language', $this->language, true);
        $criteria->compare('translation', $this->translation, true);

        return new CActiveDataProvider($this, array(
                    'criteria' => $criteria,
                ));
    }

    // Statics
    // -------------------------------------------------------------------------

    /**
     *
     * @param string $key for the lookup 
     * @return array|string the hole lookup array or the match
     */
    public static function LanguageList($key = NULL) {
        // define in protected/config/main.php which languages are supported
        $array = Yii::app()->params['supportedLanguages']; 
        if ($key !== NULL)
            return $array[$key];
        else
            return $array;
    }

    // Getter and Setter
    // -------------------------------------------------------------------------

    public function getLanguageName() {
        return self::LanguageList($this->language);
    }
}

The defaultController.php ¶

To manipulate the database records, we now create the needed actions. Since the modules\i18n\Controller\defaultController.php created by Gii is nearly empty, we have to put in some logic which is again merely the same as Gii would produce with any other Controller. For the sake of demonstration, firstly come the untouched default actions:

<?php
class DefaultController extends Controller {

    /**
     * @return array action filters
     */
    public function filters() {
        return array(
            'accessControl', // perform access control for CRUD operations
        );
    }

    /**
     * Specifies the access control rules.
     * This method is used by the 'accessControl' filter.
     * @return array access control rules
     */
    public function accessRules() {
        return array(
            array('allow', // allow authenticated user to perform 'create' and 'update' actions
                'actions' => array('index', 'view', 'create', 'update', 'delete'),
                'users' => array('@'),
            ),
            array('deny', // deny all users
                'users' => array('*'),
            ),
        );
    }

    /**
     * Displays a particular model.
     * @param integer $id the ID of the model to be displayed
     */
    public function actionView($id) {
        $this->render('view', array(
            'model' => $this->loadModel($id),
        ));
    }

    /**
     * Manages all models.
     */
    public function actionIndex() {
        $this->layout = '//layouts/column2';
        $model = new SourceMessage('search');
        $model->unsetAttributes();  // clear any default values
        if (isset($_GET['SourceMessage']))
            $model->attributes = $_GET['SourceMessage'];

        $this->render('admin', array(
            'model' => $model,
        ));
    }

    /**
     * Returns the data model based on the primary key given in the GET variable.
     * If the data model is not found, an HTTP exception will be raised.
     * @param integer the ID of the model to be loaded
     */
    public function loadModel($id) {
        $model = SourceMessage::model()->findByPk($id);
        if ($model === null)
            throw new CHttpException(404, 'The requested page does not exist.');
        return $model;
    }
    // default actions so far
}

And here is the modified logic we have to have a closer look at. Basically the complexity stems from the multiple model Validation form. Please refer to the wiki #19 How to use a single form to collect data for two or more models? to have a better insight on this topic.

// here come the modified actions
    
    /**
     * Creates a new model.
     * If creation is successful, the browser will be redirected to the 'view' page.
     */
    public function actionCreate() {
        $model = new SourceMessage;
        $messages = array();
        // create a Form for each language
        foreach (Message::LanguageList() as $lang => $language) {
            $messages[$lang] = new Message('create');
        }
        if (isset($_POST['SourceMessage']) && isset($_POST['Message'])) {
            $model->attributes = $_POST['SourceMessage'];
            if ($model->save()) {
                $valid = true;
                // validate translations
                foreach ($messages as $i => $item) {
                    if (isset($_POST['Message'][$i]))
                        $item->attributes = $_POST['Message'][$i];
                    $item->id = $model->id;
                    $item->language = $i;
                    $valid = $item->validate() && $valid;
                }
                if ($valid) {
                    foreach ($messages as $item)
                        $item->save();
                    Yii::app()->user->setFlash('success', Yii::t('App', 'Your changes have been saved.'));
                    $this->redirect(array('index'));
                }
            }
        }

        $this->render('create', array(
            'model' => $model,
            'messages' => $messages,
        ));
    }

    /**
     * Updates a particular model.
     * If update is successful, the browser will be redirected to the 'view' page.
     * @param integer $id the ID of the model to be updated
     */
    public function actionUpdate($id) {
        $model = $this->loadModel($id);
        $items = array();
        foreach (Message::LanguageList() as $lang => $language) {
            $_message = $model->Messages(array('condition' => "language=\"{$lang}\""));
            if (!empty($_message))
                $items[$lang] = $_message[0];
            else {
                $items[$lang] = new Message('create');
                $items[$lang]->id = $model->id;
                $items[$lang]->language = $lang;
            }
        }

        if (isset($_POST['SourceMessage']) && isset($_POST['Message'])) {
            $model->attributes = $_POST['SourceMessage'];
            if ($model->save()) {
                $valid = true;
                foreach ($items as $i => $item) {
                    if (isset($_POST['Message'][$i]))
                        $item->attributes = $_POST['Message'][$i];
                    $valid = $item->validate() && $valid;
                }
                if ($valid) {
                    foreach ($items as $item)
                        $item->save();
                    Yii::app()->user->setFlash('success', Yii::t('App', 'Your changes have been saved'));
                    $this->redirect(array('index'));
                }
            }
        }

        $this->render('update', array(
            'model' => $model,
            'messages' => $items,
        ));
    }

    /**
     * Deletes a particular model.
     * If deletion is successful, the browser will be redirected to the 'admin' page.
     * @param integer $id the ID of the model to be deleted
     */
    public function actionDelete($id) {
        if (Yii::app()->request->isPostRequest) {
            // we only allow deletion via POST request
            $model = $this->loadModel($id);
            foreach ($model->Messages as $message)
                $message->delete();

            $model->delete();
            Yii::app()->user->setFlash('success', Yii::t('App', 'Your changes have been saved.'));
            // if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser
            if (!isset($_GET['ajax']))
                $this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('index'));
        }
        else
            throw new CHttpException(400, 'Invalid request. Please do not repeat this request again.');
    }    

As you can see, we create, update and delete SourceMessage and Message together. If you find a more elegant way to create and update these models – let me know.

With the Module registered, the database initiated, the Controller and Models in place it’s time to get some output.

The view files ¶

Now it’s time to implement the actual view files, allowing letting the logic do its part. Again, we only create the necessary files with mostly default content. Please feel free to add your shim to each of those files if needed.

modules\i18n\views\default\_form.php
modules\i18n\views\default\admin.php
modules\i18n\views\default\create.php
modules\i18n\views\default\update.php

Here is the code for modules\i18n\views\default\create.php:

<?php
$this->breadcrumbs = array(
    Yii::t('App', 'Templates') => array('index'),
    Yii::t('App', 'Create'),
);
?>
<div class="right">
    <?php echo CHtml::link(Yii::t('App', 'Back to Overview'), array('index')); ?>
</div>
<h1><?php echo Yii::t('App', 'Create Template'); ?></h1>

<?php echo $this->renderPartial('_form', array('model' => $model, 'messages' => $messages)); ?>

Here is the code for modules\i18n\views\default\update.php:

<?php
$this->breadcrumbs = array(
    Yii::t('App', 'Templates') => array('index'),
    Yii::t('App', 'Update'),
);
?>
<div class="right">
    <?php echo CHtml::link(Yii::t('App', 'Back to Overview'), array('index')); ?>
</div>
<h1><?php echo Yii::t('App', 'Update Template') . " #$model->id"; ?></h1>

<?php echo $this->renderPartial('_form', array('model' => $model, 'messages' => $messages)); ?>

Here is the code for modules\i18n\views\default\_form.php:

<div class="form">
    <?php $form = $this->beginWidget('CActiveForm', array('id' => 'source-message-form',)); ?>
    <p class="note"><?php echo Yii::t('App', 'Fields with <span class="required">*</span> are required.'); ?></p>

    <?php echo $form->errorSummary($model); ?>

    <fieldset>
        <div class="row">
            <div class="span5">
                <?php echo $form->labelEx($model, 'category'); ?>
                <?php echo $form->dropDownList($model, 'category', I18nModule::CategoryList()); ?>
                <?php echo $form->error($model, 'category'); ?>
            </div>

            <div class="span5">
                <?php echo $form->labelEx($model, 'message'); ?>
                <?php echo $form->textField($model, 'message', array('maxlength' => 128)); ?>
                <?php echo $form->error($model, 'message'); ?>
            </div>
        </div>
    </fieldset>
    <?php foreach ($messages as $i => $message): ?>
        <fieldset>
            <legend>
                <?php echo CHtml::activeLabelEx($message, "[$i]language"); ?>
                <strong>&nbsp;<?php echo Message::LanguageList($i); ?></strong>
            </legend>
            <div class="row">
                <?php echo $form->labelEx($message, "[$i]translation"); ?>
                <?php echo $form->textArea($message, "[$i]translation", array('rows' => 6, 'cols' => 50)); ?>
                <?php echo $form->error($message, "[$i]translation"); ?>
            </div>
        </fieldset>
    <?php endforeach; // $message     ?>

    <div class="row buttons">
        <?php echo CHtml::submitButton(Yii::t('App', 'Save')); ?>
        <?php if (!$model->isNewRecord) : ?>
            <?php
            echo CHtml::link(Yii::t('App', 'Delete'), '#', array(
                'submit' => array('delete', 'id' => $model->id),
                'confirm' => Yii::t('App', 'Are you sure to delete this item?'),
                // if you have enabled CsrfValidation add this line
                'params' => array('reg' => 'new', 'YII_CSRF_TOKEN' => Yii::app()->request->csrfToken),
                'class' => 'right',
                'title' => Yii::t('App', 'Delete'),
            ));
            ?>
        <?php endif; // isNewRecord ?>
    </div>
    <?php $this->endWidget(); ?>
</div><!-- form -->

Here is the code for modules\i18n\views\default\_form.php:

<?php
$this->breadcrumbs = array(
    Yii::t('App', 'Templates') => array('index'),
    Yii::t('App', 'Manage'),
);
?>
<div class="right">
    <?php
    echo CHtml::link(Yii::t('App', 'New'), array('create'), array(
        'title' => Yii::t('App', 'Create New Item'),
        'id' => 'create-new-item',
    ));
    ?>
</div>
<h1><?php echo Yii::t('App', 'Manage Templates'); ?></h1>
<?php
$this->widget('zii.widgets.grid.CGridView', array(
    'id' => 'source-message-grid',
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array(
        array(
            'name' => 'category',
            'filter' => I18nModule::CategoryList(),
        ),
        array(
            'name' => 'message',
        ),
        array(
            'name' => 'Messages.translation',
            'value' => '$data->translation',
        ),
        array(
            'class' => 'CButtonColumn',
            'template' => '{update}{delete}',
        ),
    ),
));
?>

I tried to melt it down to the only necessary code. But if you go over it, you should not see nothing new. Pretty much straight forward.

Working example ¶

So how to use this setup? Answer: the same way as with [CDbMessageSource]. E.g. create a new entry called #home#, select the category App and past in some text for de and en. To display that text simply use:

<?php echo Yii::t('App', '#home#', array(), 'i18n');?>

Where i18n is the reference to our protected\config\main.php setup. Not the module name but the components\i18n array. To switch the language, simply switch the language settings in the protected\config\main.php - or use you favorite method (cf. Setting and maintaining the language in Application (i18n) ).

Going live and DVCS ¶

Now that everything is set up there is only one thing to pay attention to: the file and folder access rights. Since the i18n.sqlite is buried deep in the application tree the folder protected\modules\i18n\data has to be chmod 755. That works fine until you firstly push and pull your DVCS.

Using hooks to chmod i18n.sqlite ¶

To my experience with Mercurial, all file (and folder) permissions will be overwritten by the hg pull -u . Therefore, Mercurial has to be instructed with a hook to chmod i18n.sqlite to 755. After init, Mercurial created a subfolder called .hg. In this folder you create or update the .hg\hgrc-file (yes without file extension): ~~~ [hooks] changegroup = .hg/hooks.sh ~~~ Now create the referenced bash script .hg/hooks.sh an put in there:

[sh]
#!/bin/zsh
# set rw sqlite
chmod 755 ./protected/modules/i18n/data/*

thats all. Now mercurial will always chmod your sqlite folder and all files within. (Since I am only familiar with mercurial, it would be a great help, if someone could post the equivalent in Git.)

Discussion ¶

It took me about three projects to come up with this solution. I am still not very comfortable with all the chmod and the "database lies within the modules-folder"-thing. But it is the idea of a module to be independent from the rest of the application. Thus, I can live with this exception.

Hopefully you get some good ideas from this wiki. If you find errors, mistakes or ever clever hints - please let me know. Thank you all for this great community. This article really comes from the urge to give something back, after finding so much good advice.