Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "schema"

Use different schema with postgresql

How to use a different schema as "public" with postgresql/symfony? Please find below the solution I use

Todo in postgresql

-- Create user
CREATE ROLE appUser LOGIN ENCRYPTED PASSWORD '' NOINHERIT VALID UNTIL 'infinity';
 
-- Create schema
CREATE SCHEMA appSchema AUTHORIZATION appUser;
 
-- Change default search path of user
ALTER USER appUser SET search_path TO appSchema, public;
 

To do in symfony

Comments line 202 in symfony-1.2.8/lib/plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/builder/sql/pgsql/PgsqlDDLBuilder.php

//$script .= "\nSET search_path TO public;";
 

Config databases.yml and propel.ini

dev:
  propel:
    param:
      classname:  DebugPDO
 
test:
  propel:
    param:
      classname:  DebugPDO
 
all:
  propel:
    class:        sfPropelDatabase
    param:
      classname:  PropelPDO
      dsn:        pgsql:host=localhost;dbname=mydb
      username:   appUser
      password:   test
      encoding:   utf8
      persistent: true
      pooling:    true
 
------------------------------------------
[propel.ini]
propel.targetPackage       = lib.model
propel.packageObjectModel  = true
propel.project             = sfTest
propel.database            = pgsql
propel.database.driver     = pgsql
propel.database.url        = pgsql:host=localhost;dbname=mydb
propel.database.creole.url = ${propel.database.url}
propel.database.user       = appUser
propel.database.password   = test
propel.database.encoding   = utf8
 
propel.addVendorInfo       = true
propel.addGenericAccessors = true
propel.addGenericMutators  = true
propel.addTimeStamp        = true
propel.addValidators       = false
 
propel.useDateTimeClass       = true
propel.defaultTimeStampFormat = Y-m-d H:i:s
propel.defaultTimeFormat      = H:i:s
propel.defaultDateFormat      = Y-m-d
 
propel.schema.validate        = false
propel.samePhpName            = false
propel.disableIdentifierQuoting     = false
propel.emulateForeignKeyConstraints = true
 
; directories
propel.home                    = .
propel.output.dir              = /exp/www/sfTest
propel.schema.dir              = ${propel.output.dir}/config
propel.conf.dir                = ${propel.output.dir}/config
propel.phpconf.dir             = ${propel.output.dir}/config
propel.sql.dir                 = ${propel.output.dir}/data/sql
propel.runtime.conf.file       = runtime-conf.xml
propel.php.dir                 = ${propel.output.dir}
propel.default.schema.basename = schema
propel.datadump.mapper.from    = *schema.xml
propel.datadump.mapper.to      = *data.xml
 
; builder settings
propel.builder.peer.class              = plugins.sfPropelPlugin.lib.builder.SfPeerBuilder
propel.builder.object.class            = plugins.sfPropelPlugin.lib.builder.SfObjectBuilder
propel.builder.objectstub.class        = plugins.sfPropelPlugin.lib.builder.SfExtensionObjectBuilder
propel.builder.peerstub.class          = plugins.sfPropelPlugin.lib.builder.SfExtensionPeerBuilder
propel.builder.objectmultiextend.class = plugins.sfPropelPlugin.lib.builder.SfMultiExtendObjectBuilder
propel.builder.mapbuilder.class        = plugins.sfPropelPlugin.lib.builder.SfMapBuilderBuilder
 
propel.builder.addIncludes  = false
propel.builder.addComments  = true
propel.builder.addBehaviors = true
 
by Giuseppe Damiani on 2009-09-09, tagged pgsql  postgresql  schema 

myPakeTransformSchemaSfguard.php

or apply transformation on sfGuard tables into your main schema

Problem

When you generate a schema with sf_guard tables from your main database with the following command, the schema contains all the tables included the sf_guard tables (it's normal).

symfony propel-build-schema xml
 

Then after, when you build the model classes, the sfGuardPlugin classes have a wrong name and are in a wrong place (lib/). There is a post on this problem, how to correctly use propel-build-schema after sfGuardPlugin installation?

symfony propel-build-model
 

Solution

To correct the schema, apply the transformation on the XML schema after the schema generation :

symfony transform-schema-sfguard xml
 

The script add in the schema for all sf_guard_* tables, the package and the phpName with the camelcase used by the plugin.

Also you have to switch off the schema in the plugin to don't have twice declaration :

mv ./plugins/sfGuardPlugin/config/schema.yml ./plugins/sfGuardPlugin/config/schema.yml.off
 

The script: myPakeTransformSchemaSfguard.php

pake_desc( 'apply transformation on sfGuard tables into your main schema' );
pake_task( 'transform-schema-sfguard', 'project_exists' );
 
function run_transform_schema_sfguard($task, $args) 
{
  // Check params
  // -- missing params ?
  if ( !count($args) > 1 ) {
    throw new Exception("You must provide a transformation to apply.\nsymfony transform-schema-sfguard\nsymfony transform-schema-sfguard xml");
  }
 
  // -- schema exists ?
  if ($args[0] == 'xml')
  {
    $schema_filename = sprintf( '%s/schema.xml', sfConfig::get('sf_config_dir') );
    if ( !file_exists($schema_filename) ) {
        throw new Exception( "Missing schema.xml" );
    }
  }
  else
  {
    $schema_filename = sprintf( '%s/schema.yml', sfConfig::get('sf_config_dir') );
    if ( !file_exists($schema_filename) ) {
        throw new Exception( "Missing schema.yml (not yet implemented)" );
    } else {
        throw new Exception( "schema.yml not yet implemented" );
    }
  }
 
  // Backup schema
  //pake_copy( $schema_filename, $schema_filename . '.previous', array('override' => true) );
 
 
  if ($args[0] == 'xml')
  {
    $handle = fopen($schema_filename, "r");
 
    // get the entire file in $contents
    $contents = '';
    while (!feof($handle))
    {
      $contents.= fread($handle, 8192);
    }
    fclose($handle);
 
    $num = preg_match_all('/<table(.*)>/i', $contents, $matches);
 
    // each table definition found
    foreach ($matches[0] as $val)
    {
      if (!preg_match('/package|phpName/i', $val) && preg_match('/name="(sf_guard_.+?)"/i', $val, $val_matches)) {
 
        $table_name = $val_matches[1];
 
        //$php_name   = sfInflector::camelize($val_matches[1]);
        $php_name = sfToolkit::pregtr($val_matches[1], array('#/(.?)#e'   => "'::'.strtoupper('\\1')",
                                                             '/(_)(.)/e'  => "strtoupper('\\2')",
                                                             '/(^)(.)/e'  => "strtolower('\\2')"));
 
        $pattern    = '/(<table.*)(name="'.$table_name.'")(.*>)/i';
        $replace    = '$1$2 package="plugins.sfGuardPlugin.lib.model" phpName="'.$php_name.'" $3';
        $contents   = preg_replace($pattern, $replace, $contents, 1, $count);
 
        pake_echo($table_name);
      } 
    }
 
    // write the result
    $handle = fopen($schema_filename, "w+");
    fwrite($handle, $contents);
    fclose($handle);
  }
}
 

Installation

Put the myPakeTransformSchemaSfguard.php script into data/tasks directory

Use

mv ./plugins/sfGuardPlugin/config/schema.yml ./plugins/sfGuardPlugin/config/schema.yml.off
 
symfony propel-build-schema xml
 
symfony transform-schema-sfguard xml
 
symfony propel-build-model
 
by Olivier LOYNET on 2008-04-09, tagged cli  pake  propel  schema  sfguard 

exclude tables from propel-build-schema

The following patch will help you exclude the schema generation of certain tables when running propel-build-schema.

This might come in handy when you use propel-build-schema along with plugin schemas.

edit $sf_symfony_lib_dir/vendor/propel-generator/classes/propel/phing/PropelCreoleTransformTask.php

    protected function createDatabaseNode($dbInfo) {
 
        $this->log("Processing database");
 
        $node = $this->doc->createElement("database");
        $node->setAttribute("name", $dbInfo->getName());
 
        if ($vendorNode = $this->createVendorInfoNode($dbInfo->getVendorSpecificInfo())) {
            $node->appendChild($vendorNode);
        }
 
        global $schema_exclude_pattern;
        $pattern = $schema_exclude_pattern;
 
        $this->log("Exclude pattern : ".$pattern);
        // create and add table nodes
        foreach($dbInfo->getTables() as $table) {
            if (preg_match($pattern,$table->getName()))
            {
                $this->log("Skipping : ".$table->getName()." ( matches exclude pattern )");
                continue;
            }
            $tableNode = $this->createTableNode($table);
            $node->appendChild($tableNode);
        }
 
        return $node;
    }
 

pattern provided via the $schema_exclude_pattern variable which can be set in config.php (kinda ugly but it works)

config.php

<?php
//
// symfony directories
$sf_symfony_lib_dir  = '/bridge/lib/symfony/1.0/lib';
$sf_symfony_data_dir = '/bridge/lib/symfony/1.0/data';
 
// skips schema creation for tables which name matches the following pattern 
// when executing propel-build-schema
$schema_exclude_pattern = "/^sf_guard.*/i";
 
by Kostas Papadimitriou on 2007-12-15, tagged creole  database  generation  propel  propelbuildschema  schema 

foreignkey to sf_guard_user

if you won´t to add a foreignkey to sf_guard_user put this on top off you schema.yml file ;)

propel:
  _attributes :          { package: "plugins.sfGuardPlugin.lib.model" }
  sf_guard_user:
    _attributes:         { phpName: sfGuardUser }
    id:

now you can simple add a foreignkey

propel:
  tbl_user_profile:
    user_id:             { type: integer, primaryKey: true, foreignTable: sf_guard_user, foreignReference: id, onDelete: cascade }

or

propel:
  tbl_user_profile:
    sf_guard_user_id:
by Gordon Franke on 2007-04-27, tagged database  foreignkey  model  plugin  propel  schema  security  user  yml 
(1 comment)