SQLMAP in Yii

Hi,

I am new in Yii, and I think what is important implement a SqlMap yii version how in Prado. That’s is very usefull for database applications.

The XML for persist the sql’s is a beautiful technique. How can I use there?

AR should be better or if the SQL is too complicated i better put in in the code. The SqlMap in Prado is amazing but pretty slow even when cached.

I also missed SqlMap from PRADO when i was porting an older application to Yii. Wei did a start some time ago, but the implementation is far from complete. So i built my own “SimpleSqlMap” class :). It’s very very basic but at least allows me to have all SQL queries in a single file. I can share it, but don’t expect too much: It’s taylored to fit my requirements and also maybe not complete.

With YII you are obliged to use ActiveRecord since all

GUI stuff is bound to it. Or you may implement a kind

of SqlMap and your personal GUI components layer. This

is what I’m going to do now, by the way. So, if you need

this, 2 people may complete this in about a week. PM

if interested.

That’s not necessarily true. CGridView/CListView (which i guess you’re referring to) use DataProviders as source for their data. So it’s not that hard to write adapters to other data sources, as long as they implement IDataProvider. Sorting might need some more adaption, though.

No, I’m refering to form management.

The deal is CHtml (intended to be generic)

depends on CModel and:

  1. Provides generic rendering functions.

  2. Provides form rendering functions.

  3. Form rendering depend on CModel.

  4. Therefore has big footprint

  5. Designed as if were using CModel-based forms on every page.

There’s no abstraction layer between model

and code generator.

waylex

CModel, not CActiveRecord.

No, there’s no mistake. Take a look at CSort - it depends on CActiveRecord.

Sorry, my fault. CSort and CPaagination do not depend on CActiveRecord.

Though, CSort has internal references to AR and there’s no abstraction

layer between those 2.

CHtml indeed only depends on a CModel and this isn’t good since CModel only

relates with form rendering and not HTML generics.

@waylex: I don’t get your point. CHtml has basic HTML tag helpers, not related to a model at all. And it has CHtml::active*() methods that assist you to build a view for a model - which is a very good thing. You’re not obliged to use these methods at all. You can still build your own HTML from scratch or write some custom HTML helpers. But if you do use a model (which is at the core of the MVC idea), you will find these helpers very useful.

To put it the other way round: If you don’t like to use a model, i wonder why you use a MVC framework at all.

What I actually mean might be illustrated in the following way:

YII: CHtml + form -> CWidget -> grid + list

My view: CHtml -> CWidget -> grid + list + form

I think what combining SqlMap and Active Record is the ideal. I view SqlMap how a data layer, and the ActiveLayer pointing to that. Maybe put Attribute in node map, with TableModel, from Database, or definition table in sqlmap new node.

<update tablemodel="users">

update … (sql)

</update>

and

(when the model is direct from database)

<tablemodel id="users" source="database">

</tablemodel>

(when the model is build in sqlmap)

<tablemodel id="users" source="database">

<field id="id" type="integer"/>

<field id="name" type="varchar(255)"/>

</tablemodel>

The ActiveRecord take the fields from TableModel, how a DataSet build on the Fly.

The advantage is robust application on several database layers.

The disadvantage, possible slowly. But, using caching techniques and depending on the scenarios can be them extremely useful, even indispensable.

Is only a idea.

Completely agree with you! ActiveRecord should be built on top of QueryBuilder.

I had a short talk with Qiang and he agreed this makes sense to be done in 1.2.

Take a look:


    // Simple select.

    TQueryBuilder::create()

        ->select('tbl_users U')

        ->limit(1,10)

        ->queryAll();

    

    // Select by PK.

    TQueryBuilder::create()

        ->select('tbl_users U')

        ->wherePk(1)

        ->queryRow();

    

    // Complex select.

    TQueryBuilder::create()

        ->select('tbl_users U', '*, Name N')

        ->where('Timestamp > ?', time())

        ->orderBy('Name ASC')

        ->groupBy('N')

        ->having('Name IS NOT NULL')

        ->queryAll();

    

    // Left join example.

    TQueryBuilder::create()

        ->select('accounts A', 'U.Name')

        ->leftJoin('payments P', 'P.UserId = A.Id AND P.Amount > ?')

        ->queryAll();

    

    // Update example.

    TQueryBuilder::create()

        ->update('payments', $data)

        ->wherePk(1)

        ->execute();

    

    // Insert example.

    TQueryBuilder::create()

        ->insert('payments', $data)

        ->execute();

And this is how ActiveRecord might be implemented:


/**

* Our ActiveRecord emulating class.

*/

abstract class TBuilderModel extends CModel

{

    private static $_builders = array();

    

    /**

    * This is similar to overriding model() of ActiveRecord.

    */

    public static function builder($tableName = null, $pk = null)

    {

        if (is_null(self::$_builder))

            return self::$_builder = new TQueryBuilder($tableName, $pk);

        else

            return self::$_builder;

    }

    

    /**

    * This is how we may emulate ActiveRecord.

    * This implies implementing other AR-related common methods.

    * For performance those methods might be an optional mixin.

    */

    public function findByPk($pk)

    {

        return self::builder()->select()->wherePk($pk)->queryRow();

    }

}


/**

* This is our AR-like model.

*/

class UserAccount extends TBuilderModel

{

    /**

    * We define our builder passing table name and primary key.

    * Last one is passed to avoide table schema querying. It

    * may be omited to automatically query PK info. Do we need

    * anything else regarding schema and builder?

    */

    public static function builder()

    {

        return parent::builder('tbl_user_accounts', 'id');

    }

    

    /**

    * And this is how users implement business logic.

    */

    public function create()

    {

        $this->setScenario('create');

        

        // 1. Inplace validation and custom query.

        if ($this->validate())

            self::builder()->insert($this->getAttributes())->execute();

        

        // 2. Use parent base class save() method.

        $this->save();

    }

    

    /**

    * This is how we implement relations. Here we have different 

    * coding approach. User doesn't have to learn ActiveRecord 

    * and does programming on demand via simple API.

    */

    public function getPayments()

    {

        return UserPayment::builder()->select()->wherePk($this->getId());

    }

    

    /**

    * This is a sample of an arbitrary query.

    */

    public function getLastUpdateTime()

    {

        return self::builder()

            ->select('MAX(timestamp)')

            ->criteria($criteria)

            ->queryScalar();

    }

}




// This is how we code in a controller

class UserAccountController extends CController

{

    public function actionCreate()

    {

        if (!isset($_POST['UserAccount']))

        {

            // Display our form.

        }

        else

        {

            $form = new UserAccount();

            $form->setAttributes($_POST['UserAccount']);

            

            // 1. Inplace validation.

            if ($form->validate() && $form->create())

            {

                // Do successfull stuff.

            }

            

            // 2. Solid interface, all hidden.

            if ($form->create())

            {

                // Do successfull stuff.

            }

        }

    }

}