PHP PropelQuickBuilder::getSQL方法代码示例

    public function testIndex()
    {
        $updatedSchema = '
<database>
  <table name="notification">
    <column autoIncrement="true" name="id" primaryKey="true" required="true" type="INTEGER" />
    <column name="target_user_id" required="true" type="INTEGER" />
    <column name="notification_type_unique_name" required="true" size="255" type="VARCHAR" />
    <column name="group_id" type="INTEGER" />
    <column name="date" required="true" type="TIMESTAMP" />
    <column name="objects" type="LONGVARCHAR" />
    <column name="is_new" defaultValue="1" required="true" type="BOOLEAN" />
    <foreign-key foreignTable="notification_type" name="FK_NOTIFICATION_TYPENOTIFICATION0">
      <reference foreign="unique_name" local="notification_type_unique_name" />
    </foreign-key>
    <index name="FK_NOTIFICATION_TARGET_USER">
      <index-column name="target_user_id" />
    </index>
    <index name="FK_NOTIFICATION_TYPENOTIFICATION">
      <index-column name="notification_type_unique_name" />
    </index>
  </table>
  <table name="notification_type">
    <column name="module_unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="unique_name" primaryKey="true" required="true" size="255" type="VARCHAR" />
    <column name="is_correction" defaultValue="0" required="true" type="BOOLEAN" />
    <column name="disabled_engine" size="255" type="VARCHAR" />
    <foreign-key foreignTable="module" name="FK_TYPENOTIFICATION_MODULE0" onDelete="CASCADE" onUpdate="CASCADE">
      <reference foreign="unique_name" local="module_unique_name" />
    </foreign-key>
    <index name="FK_TYPENOTIFICATION_MODULE">
      <index-column name="module_unique_name" />
    </index>
  </table>
  <table name="module">
    <column autoIncrement="true" name="id" primaryKey="true" required="true" type="INTEGER" />
    <column name="unique_name" required="true" size="255" type="VARCHAR" />
    <column name="label" primaryString="true" required="true" size="255" type="VARCHAR" />
    <column name="description" required="true" size="255" type="VARCHAR" />
  </table>
</database>
';
        $actual = "\n# This is a fix for InnoDB in MySQL >= 4.1.x\n# It \"suspends judgement\" for fkey relationships until are tables are set.\nSET FOREIGN_KEY_CHECKS = 0;\n\n-- ---------------------------------------------------------------------\n-- notification\n-- ---------------------------------------------------------------------\n\nDROP TABLE IF EXISTS `notification`;\n\nCREATE TABLE `notification`\n(\n    `id` INTEGER NOT NULL AUTO_INCREMENT,\n    `target_user_id` INTEGER NOT NULL,\n    `notification_type_unique_name` VARCHAR(255) NOT NULL,\n    `group_id` INTEGER,\n    `date` DATETIME NOT NULL,\n    `objects` TEXT,\n    `is_new` TINYINT(1) DEFAULT 1 NOT NULL,\n    PRIMARY KEY (`id`),\n    INDEX `FK_NOTIFICATION_TARGET_USER` (`target_user_id`),\n    INDEX `FK_NOTIFICATION_TYPENOTIFICATION` (`notification_type_unique_name`),\n    CONSTRAINT `FK_NOTIFICATION_TYPENOTIFICATION0`\n        FOREIGN KEY (`notification_type_unique_name`)\n        REFERENCES `notification_type` (`unique_name`)\n) ENGINE=InnoDb;\n\n-- ---------------------------------------------------------------------\n-- notification_type\n-- ---------------------------------------------------------------------\n\nDROP TABLE IF EXISTS `notification_type`;\n\nCREATE TABLE `notification_type`\n(\n    `module_unique_name` VARCHAR(255) NOT NULL,\n    `unique_name` VARCHAR(255) NOT NULL,\n    `is_correction` TINYINT(1) DEFAULT 0 NOT NULL,\n    `disabled_engine` VARCHAR(255),\n    PRIMARY KEY (`module_unique_name`,`unique_name`),\n    INDEX `FK_TYPENOTIFICATION_MODULE` (`module_unique_name`),\n    INDEX `I_referenced_FK_NOTIFICATION_TYPENOTIFICATION0_1` (`unique_name`),\n    CONSTRAINT `FK_TYPENOTIFICATION_MODULE0`\n        FOREIGN KEY (`module_unique_name`)\n        REFERENCES `module` (`unique_name`)\n        ON UPDATE CASCADE\n        ON DELETE CASCADE\n) ENGINE=InnoDb;\n\n-- ---------------------------------------------------------------------\n-- module\n-- ---------------------------------------------------------------------\n\nDROP TABLE IF EXISTS `module`;\n\nCREATE TABLE `module`\n(\n    `id` INTEGER NOT NULL AUTO_INCREMENT,\n    `unique_name` VARCHAR(255) NOT NULL,\n    `label` VARCHAR(255) NOT NULL,\n    `description` VARCHAR(255) NOT NULL,\n    PRIMARY KEY (`id`)\n) ENGINE=InnoDb;\n\n# This restores the fkey checks, after having unset them earlier\nSET FOREIGN_KEY_CHECKS = 1;\n";
        $platform = new MysqlPlatform();
        $platform->setDefaultTableEngine('InnoDb');
        $updatedBuilder = new PropelQuickBuilder();
        $updatedBuilder->setPlatform($platform);
        $updatedBuilder->setSchema($updatedSchema);
        $sql = $updatedBuilder->getSQL();
        $this->assertEquals($actual, $sql);
    }

    public function setUp()
    {
        if (!class_exists('ArchivableTest1')) {
            $schema = <<<EOF
<database name="archivable_behavior_test_0">

\t<table name="archivable_test_1">
\t\t<column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t\t<column name="age" type="INTEGER" />
\t\t<column name="foo_id" type="INTEGER" />
\t\t<foreign-key foreignTable="archivable_test_2">
\t\t\t<reference local="foo_id" foreign="id" />
\t\t</foreign-key>
\t\t<index>
\t\t\t<index-column name="title" />
\t\t\t<index-column name="age" />
\t\t</index>
\t\t<behavior name="archivable" />
\t</table>

\t<table name="archivable_test_2">
\t\t<column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t\t<behavior name="archivable" />
\t</table>

\t<table name="archivable_test_2_archive">
\t\t<column name="id" required="true" primaryKey="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t</table>

\t<table name="archivable_test_3">
\t\t<column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t\t<column name="age" type="INTEGER" />
\t\t<column name="foo_id" type="INTEGER" />
\t\t<behavior name="archivable">
\t\t\t<parameter name="log_archived_at" value="false" />
\t\t\t<parameter name="archive_table" value="my_old_archivable_test_3" />
\t\t\t<parameter name="archive_on_insert" value="true" />
\t\t\t<parameter name="archive_on_update" value="true" />
\t\t\t<parameter name="archive_on_delete" value="false" />
\t\t</behavior>
\t</table>

\t<table name="archivable_test_4">
\t\t<column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t\t<column name="age" type="INTEGER" />
\t\t<behavior name="archivable">
\t\t\t<parameter name="archive_class" value="FooArchive" />
\t\t</behavior>
\t</table>

</database>
EOF;
            $builder = new PropelQuickBuilder();
            $builder->setSchema($schema);
            self::$generatedSQL = $builder->getSQL();
            $builder->build();
        }
    }

    public function testModiFyTableUsesCustomI18nLocaleLength()
    {
        $schema = <<<EOF
<database name="i18n_behavior_test_0">
    <table name="i18n_behavior_test_0">
        <column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" />
        <behavior name="i18n">
            <parameter name="locale_length" value="6" />
        </behavior>
    </table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $expected = <<<EOF
-----------------------------------------------------------------------
-- i18n_behavior_test_0_i18n
-----------------------------------------------------------------------

DROP TABLE IF EXISTS [i18n_behavior_test_0_i18n];

CREATE TABLE [i18n_behavior_test_0_i18n]
(
    [id] INTEGER NOT NULL,
    [locale] VARCHAR(6) DEFAULT 'en_US' NOT NULL,
    PRIMARY KEY ([id],[locale])
);
EOF;
        $this->assertContains($expected, $builder->getSQL());
    }

    public function testModiFyTableUsesCustomLocaleDefault()
    {
        $schema = <<<EOF
<database name="i18n_behavior_test_0">
\t<table name="i18n_behavior_test_0">
\t\t<column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" />
\t\t<behavior name="i18n">
\t\t\t<parameter name="default_locale" value="fr_FR" />
\t\t</behavior>
\t</table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $expected = <<<EOF
-----------------------------------------------------------------------
-- i18n_behavior_test_0_i18n
-----------------------------------------------------------------------

DROP TABLE i18n_behavior_test_0_i18n;

CREATE TABLE i18n_behavior_test_0_i18n
(
\tid INTEGER NOT NULL,
\tlocale VARCHAR(5) DEFAULT 'fr_FR' NOT NULL,
\tPRIMARY KEY (id,locale)
);
EOF;
        $this->assertContains($expected, $builder->getSQL());
    }

    /**
     * tests if i18n_pk_name options sets the right pk name in i18n table
     */
    public function testModifyTableUseCustomPkName()
    {
        $schema = <<<EOF
<database name="i18n_behavior_test_0">
    <table name="i18n_behavior_test_0">
        <column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" />
        <behavior name="i18n">
            <parameter name="default_locale" value="fr_FR" />
            <parameter name="i18n_pk_name" value="custom_id" />
        </behavior>
    </table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        // checks id in base table
        $table = $builder->getDatabase()->getTable('i18n_behavior_test_0');
        $this->assertTrue($table->hasColumn('id'));
        // checks id in i18n table
        $i18nTable = $builder->getDatabase()->getTable('i18n_behavior_test_0_i18n');
        $this->assertTrue($i18nTable->hasColumn('custom_id'));
        // checks foreign key
        $fkList = $i18nTable->getColumnForeignKeys('custom_id');
        $this->assertEquals(count($fkList), 1);
        $fk = array_pop($fkList);
        $this->assertEquals($fk->getForeignTableName(), 'i18n_behavior_test_0');
        $this->assertEquals($fk->getForeignColumnNames(), '[id]');
        $expected = <<<EOF
-----------------------------------------------------------------------
-- i18n_behavior_test_0_i18n
-----------------------------------------------------------------------

DROP TABLE IF EXISTS [i18n_behavior_test_0_i18n];

CREATE TABLE [i18n_behavior_test_0_i18n]
(
    [custom_id] INTEGER NOT NULL,
    [locale] VARCHAR(5) DEFAULT 'fr_FR' NOT NULL,
    PRIMARY KEY ([custom_id],[locale])
);
EOF;
        $this->assertContains($expected, $builder->getSQL());
    }

    public function testDatabaseLevelBehavior()
    {
        $schema = <<<EOF
<database name="archivable_behavior_test_0">
\t<behavior name="archivable" />
\t<table name="archivable_test_01">
\t\t<column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
\t\t<column name="title" type="VARCHAR" size="100" primaryString="true" />
\t\t<behavior name="archivable" />
\t</table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $builder->getSQL();
    }

    /**
     * @dataProvider logSchemaDataProvider
     */
    public function testModifyTableAddsVersionTableLogColumns($schema)
    {
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $expected = <<<EOF
-----------------------------------------------------------------------
-- versionable_behavior_test_0_version
-----------------------------------------------------------------------

DROP TABLE [versionable_behavior_test_0_version];

CREATE TABLE [versionable_behavior_test_0_version]
(
\t[id] INTEGER NOT NULL,
\t[bar] INTEGER,
\t[version] INTEGER DEFAULT 0,
\t[version_created_at] TIMESTAMP,
\t[version_created_by] VARCHAR(100),
\t[version_comment] VARCHAR(255),
\tPRIMARY KEY ([id],[version])
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([id]) REFERENCES versionable_behavior_test_0 ([id])
EOF;
        $this->assertContains($expected, $builder->getSQL());
    }

    public function testDatabaseLevelBehavior()
    {
        $schema = <<<EOF
<database name="versionable_behavior_test_0">
    <behavior name="versionable" />
    <table name="versionable_behavior_test_0">
        <column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" />
        <column name="bar" type="INTEGER" />
    </table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $expected = <<<EOF
-----------------------------------------------------------------------
-- versionable_behavior_test_0_version
-----------------------------------------------------------------------

DROP TABLE IF EXISTS versionable_behavior_test_0_version;

CREATE TABLE versionable_behavior_test_0_version
(
    id INTEGER NOT NULL,
    bar INTEGER,
    version INTEGER DEFAULT 0 NOT NULL,
    PRIMARY KEY (id,version)
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY (id) REFERENCES versionable_behavior_test_0 (id)
EOF;
        $this->assertContains($expected, $builder->getSQL());
    }

    public function testGeneratedSqlForMySQL()
    {
        $schema = <<<XML
<database name="default">
    <table name="thread">
        <column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
    </table>
    <table name="post">
        <column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" />
        <column name="body" type="VARCHAR" required="true" primaryString="true" />

        <foreign-key foreignTable="thread" onDelete="cascade">
            <reference local="thread_id" foreign="id" />
        </foreign-key>

        <behavior name="nested_set">
            <parameter name="use_scope" value="true" />
            <parameter name="scope_column" value="thread_id" />
        </behavior>

        <vendor type="mysql">
            <parameter name="Engine" value="InnoDB"/>
        </vendor>
    </table>
</database>
XML;
        $expectedSql = <<<SQL

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- thread
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `thread`;

CREATE TABLE `thread`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM;

-- ---------------------------------------------------------------------
-- post
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `post`;

CREATE TABLE `post`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `body` VARCHAR(255) NOT NULL,
    `tree_left` INTEGER,
    `tree_right` INTEGER,
    `tree_level` INTEGER,
    `thread_id` INTEGER,
    PRIMARY KEY (`id`),
    INDEX `post_FI_1` (`thread_id`),
    CONSTRAINT `post_FK_1`
        FOREIGN KEY (`thread_id`)
        REFERENCES `thread` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;

SQL;
        $builder = new PropelQuickBuilder();
        $builder->setPlatform(new MysqlPlatform());
        $builder->setSchema($schema);
        $this->assertEquals($expectedSql, $builder->getSQL());
    }

    public function testDatabaseLevelBehavior()
    {
        $schema = <<<EOF
<database name="versionable_behavior_test_0">
\t<behavior name="versionable" />
\t<table name="versionable_behavior_test_0">
\t\t<column name="id" primaryKey="true" type="INTEGER" autoIncrement="true" />
\t\t<column name="bar" type="INTEGER" />
\t</table>
</database>
EOF;
        $builder = new PropelQuickBuilder();
        $builder->setSchema($schema);
        $builder->getSQL();
    }