add behavior i18n, then diff, then migrate, data losted

[dongww]

<?xml version="1.0" encoding="UTF-8"?>
<database name="bookstore" defaultIdMethod="native">
    <table name="author" phpName="Author">
        <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
        <column name="first_name" type="varchar" size="128" required="true"/>
        <column name="middle_name" type="varchar" size="128" required="false"/>
        <column name="last_name" type="varchar" size="128" required="true"/>
    </table>
</database>

run code:

<?php
require __DIR__ . '/vendor/autoload.php';
require __DIR__ . '/generated-conf/config.php';

$author = new Author();
$author->setFirstName('Jane');
$author->setLastName('Austen');
$author->save();

edit schema.xml , add behavior i18n

<?xml version="1.0" encoding="UTF-8"?>
<database name="bookstore" defaultIdMethod="native">
    <table name="author" phpName="Author">
        <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
        <column name="first_name" type="varchar" size="128" required="true"/>
        <column name="middle_name" type="varchar" size="128" required="false"/>
        <column name="last_name" type="varchar" size="128" required="true"/>
+       <behavior name="i18n">
+           <parameter name="i18n_columns" value="first_name, last_name" />
+       </behavior>
    </table>
</database>

then diff, then migrate, data loss.

[marcj]

How looked the migration file?

[dongww]

<?php

/**
 * Data object containing the SQL and PHP code to migrate the database
 * up to version 1411672774.
 * Generated on 2014-09-25 21:19:34 
 */
class PropelMigration_1411672774
{
    public $comment = '';

    public function preUp($manager)
    {
        // add the pre-migration code here
    }

    public function postUp($manager)
    {
        // add the post-migration code here
    }

    public function preDown($manager)
    {
        // add the pre-migration code here
    }

    public function postDown($manager)
    {
        // add the post-migration code here
    }

    /**
     * Get the SQL statements for the Up migration
     *
     * @return array list of the SQL strings to execute for the Up migration
     *               the keys being the datasources
     */
    public function getUpSQL()
    {
        return array (
  'bookstore' => '
PRAGMA foreign_keys = OFF;

CREATE TABLE author_i18n
(
    id INTEGER NOT NULL,
    locale VARCHAR(5) DEFAULT \'en_US\' NOT NULL,
    first_name VARCHAR(128) NOT NULL,
    last_name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id,locale),
    UNIQUE (id,locale),
    FOREIGN KEY (id) REFERENCES author (id)
        ON DELETE CASCADE
);

CREATE TEMPORARY TABLE author__temp__54246ac633e6a AS SELECT id,first_name,middle_name,last_name FROM author;
DROP TABLE author;

CREATE TABLE author
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    middle_name VARCHAR(128),
    UNIQUE (id)
);

INSERT INTO author (id, middle_name) SELECT id, middle_name FROM author__temp__54246ac633e6a;
DROP TABLE author__temp__54246ac633e6a;

PRAGMA foreign_keys = ON;
',
);
    }

    /**
     * Get the SQL statements for the Down migration
     *
     * @return array list of the SQL strings to execute for the Down migration
     *               the keys being the datasources
     */
    public function getDownSQL()
    {
        return array (
  'bookstore' => '
PRAGMA foreign_keys = OFF;

DROP TABLE IF EXISTS author_i18n;

ALTER TABLE author

  ADD first_name VARCHAR(128) NOT NULL,

  ADD last_name VARCHAR(128) NOT NULL;

PRAGMA foreign_keys = ON;
',
);
    }

}

[dongww]

database is sqlite

[kitsunde]

I had a duplicate issue in https://github.com/propelorm/Propel2/issues/831 except I wasn't getting any INSERT statements. There's also another related issue where if I remove a behavior it should do an UPDATE to move the en_US data back which I had to manually add to my migrations.

[motin]

I vote for renaming the original i18n columns instead of dropping them, for instance by suffixing them with "_in_source_locale" or similar. This ensures that no data is lost, allows for data migrations to be easily created and at the same time making it straightforward to keep using the "_in_source_locale" fields as the natural place to store the original contents before a source locale and/or a translation locale is defined. The user can then drop the columns manually when/if they are no longer necessary.

[dereuromark]

Is someone able to make a PR here with suggested changes?