I frequently create new projects in symfony; however, I often forget the exact steps that are required. I have referenced my previous workflow a zillion times, but many of the steps have changed with version 1.4.

$ cd ~
$ mkdir sfproject
$ cd sfproject
$ symfony propel
$ symfony generate:project sfproject
$ symfony generate:app frontend

Create schema.yml
Create new database in phpMyAdmin
$ symfony configure:database "mysql:host=db.domain.com;dbname=actualdbname" username passwd

$ symphony propel:build-model
$ symphony propel:build-sql
$ symfony propel:insert-sql
$ symfony propel:build-forms
$ symfony propel:generate-module frontend article Article
$ symfony cache:clear

Additional data on symfony tasks is available.

Symfony 1.0 and MSSQL

February 18th, 2009

Symfony 1.0 has a never-going-to-be-fixed bug that prevents it from building the database automagically. Microsoft decided not to support the old php_mssql connection methods; therefore, it makes sense for symfony to abandon it as well.

Sadly, symfony 1.0 does not support the new database connection methods either… so we are stuck with hacks and workarounds.

Here is the fix for this error:

“Native Error: Incorrect syntax near ‘_PK’”

Change line 113 of engine/builder/sql/mssql/MssqlDDLBuilder.php from:

$lines[] = “CONSTRAINT “.$this->quoteIdentifier($table->getName()).”_PK
PRIMARY KEY (“.$this->getColumnList($table->getPrimaryKey()).”)”;

to:

$lines[] = “CONSTRAINT “.$this->quoteIdentifier($table->getName().”_PK”).
” PRIMARY KEY (“.$this->getColumnList($table->getPrimaryKey()).”)”;

The original propel ticket provided this juicy info.

This will only work with SQL 2005 and prior.  SQL 2008 does not work.  Symfony commands are 1.0 style and other versions of symfony will need to use the updated command structure.

SQL:

1. Create a database within SQL 2005 (example: testdb)
2. Authenticate the db with SQL Server Authentication (sa)

Windows Symfony Install:

1.Within the main php directory replace ntwdblib.dll with version 2000.80.194.0.  Google search for version or obtain from one of the links within php mssql_connect documentation.
2. Stop and restart apache
3. Clear cache in symfony.  If you receive pake errors with inverted slashes during “symfony cc”, then edit your spPakeMisc.php file and retry cache clear.
4. Edit your databases.yml with the following information.  Replace the password, ip address (or localhost), and database name with your information.  Remember that yml files must have spaces, not tabs.

all:
  propel:
    class:          sfPropelDatabase
    param:
       dsn:            mssql://sa:[email protected]/testdb

5. Paste your dsn line above into the propel.database.url section of propel.ini. Paste the dsn line minus the database name into the propel.database.createUrl section.
6. Clear cache in symfony.
7. Be sure your schema.yml is correct.
8. From your project directory do symfony propel-build-model and be sure there are no errors.
9. From your project directory do symfony propel-build-sql
10. Although it is unlikely to work, you can try to do a symfony propel-insert-sql
11. If it inserts correctly, then you are set.  Most likely you will notice that all the SQL statements did not execute successfully.  You will probably see Native Errors regarding “Incorrect syntax near \’_PK\’.]” along with other errors.  This is fixed in propel 1.3 if you want to try to figure out how to upgrade symfony or install an updated propel plugin.  Otherwise, you are going to want to manually correct your propel generated sql based on the recommendations in this thread.  If your project is myproject, then your generated sql lives here:  c:\myproject\data\sql\lib.model.schema.sql

I’ll quote the important global search and replace information here:

(Ignore the quotes  )
seek for: “]_PK”
replace with: “_PK]”

Then you will also need to alter all the strings:

seek for: “ON UPDATE RESTRICT ON DELETE RESTRICT”
replace for: “ON UPDATE NO ACTION ON DELETE NO ACTION”

In addition you will need to also move all the entries (their block of statements: BEGIN AND END included) that carry:
” ON UPDATE NO ACTION ON DELETE NO ACTION”
to the end of the script

and finally move also all the
” ADD CONSTRAINT “
entries (their block of statements: BEGIN AND END included) to the bottom of the file

12. Copy this file to somewhere on your sql server.  Double-click to open it within SQL server and “execute” to populate the sql into your database.
13. Generate frontend and backend applications within symfony.  Generation of an propel-init-admin in the backend is a quick way to test database read/write.

Notes:
Be sure that the correct version of the dll file is running.  Restarting apache should be enough, but you know how windows dll files are.  If something doesn’t seem to be working correctly, clear the symfony cache.  You know how the symfony cache can bugger up things.

Good luck.

, , , , ,

I have spent the last two days trying desperately to connect my symfony 1.0 application to a MS SQL Server 2008. It is just currently not possible… I believe.

Starting with SQL 2005, Microsoft decided to stop supporting the internal API from ntwdblib.dll and thus broke the mssql-connect method. Some people are still having success using the mssql-connect function with hacked/updated ntwdblib.dll files. I have not seen success with this in SQL 2008, however.

In order to avoid utilizing outdated methods, I started working with Microsoft’s new recommended method ODBC. Connecting with to the SQL Server 2008 with ODBC and basic php was simple. Therefore, I started hacking away at symfony configuration files to use the ODBC connect methods. No luck.

Symfony uses Propel for object-based database access. When selecting mssql in propel.ini or databases.yml, propel will attempt to connect using the mssql-connect dependent functions as mentioned above. Hopefully there will be full PDO (and thus ODBC) support in Propel 1.3.

Unfortunately, Propel 1.3 is not supported in Symfony 1.0. Propel 1.3 will be the default installation in Symfony 1.2. Some people may have success running a propel 1.3 plugin.

I would love to hear some success stories that contradict my findings…

, , , , ntwdblib.dll

I’m posting this to my blog because there has got to be an easier way to do this. However, this work around at least got me running again.

Here is a typical update query with propel:

$con = Propel::getConnection();

// select from...
$c1 = new Criteria();
$c1->add(CommentPeer::POST_ID, $post_id);

// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);

BasePeer::doUpdate($c1, $c2, $con);

Logically, a blank criteria should select all rows for update. However, this code does not work. To me, it seems this should change RATING to 5 for every row; however, it actually does nothing…

$con = Propel::getConnection();

// select from...
$c1 = new Criteria();

// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);

BasePeer::doUpdate($c1, $c2, $con);

So here is the work around I am currently using…

$con = Propel::getConnection();

// select from...
$c1 = new Criteria();
//WORK AROUND
$c1->add(CommentPeer::RATING, 5, Criteria::NOT_EQUAL);

// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);

BasePeer::doUpdate($c1, $c2, $con);

So my work around is to select all where the rating is NOT 5. This works because if the rating is 5, it doesn’t need to be updated. I guess less gracefully you could select some impossible RATING value which would just grab all.

I’m a total symfony newbie, so somebody can feel free to correct me.

At this point I know so little about symfony it is sad. I have had to reinstall my project several times now. I keep getting the steps out of order… so hopefully this will remind me the correct order in case I have to do it again.

573 $ cd ~
574 $ mkdir census
575 $ cd census
576 $ symfony init-project census
577 $ symfony init-app frontend

Create new database in phpMyAdmin

Edit entries in the config directory:

    – Create database scheme in schema.yml
    – Uncomment and point databases.yml to the correct db
    – Point propel.database.createUrl and propel.database.url to the correct db in propel.ini

579 $ symfony propel-build-model
580 $ symfony propel-build-sql
581 $ symfony propel-insert-sql
582 $ symfony propel-generate-crud frontend patient Patient
583 $ symfony cc frontend config
584 $ symfony init-app backend

Obviously, census is the name of my project and the directory. patient/Patient relates my db.

Technorati Tags: