Yii Framework Forum: Saas Multi-Tenant Separate Db - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Saas Multi-Tenant Separate Db Rate Topic: -----

#21 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 25 April 2014 - 03:56 PM

View Postyomer, on 25 April 2014 - 03:33 PM, said:

@JFReyesIt does help a lot. Just to clear your point 2.a. The triggers in every table force the tenant_dbu when inserting a new record, but when the AppStaff inserts a record, it skips the trigger, because he would have control over which tenant_dbu and tenant_id to assign to that record. Is this right?


Yes.

View Postyomer, on 25 April 2014 - 03:33 PM, said:

In this first lines, could I remove the requirement of getting the User model if I have a tenant_id already stored in session?


Yes but I would be concerned about security as sessions can be hacked into, but there are ways to protect against it.

View Postyomer, on 25 April 2014 - 03:33 PM, said:

If the tenant has his own DB, then the connectionString changes to go to look for DB: db_[dbusername]. The credentials are the same, what changes is where are the credentials for the tenant going to be valid, wither in the master db or the independent db.When the connection is made to either of those, the session will already have the tenant_id to write to the tables and populate. Can the primary/foreign key relationship between all tables and the tenant table be omitted in favor of using the current user's stored tenant_id and tenant_dbu to view and modify the tables?


I wouldn't trust the app code to maintain referential integrity in the database, there are too many ways that can go wrong.
José
0

#22 User is offline   yomer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 01-April 14

Posted 29 April 2014 - 01:01 AM

After preparing some models, I'm trying to create a Tenant and a User. For that, I allowed for anyone to create in the rules section. But I'm getting a Property "CStringValidator.0" is not defined. error when I try to create either the Tenant or the User. I thought the error was in the model, but I did everything accordingly only to get this error.

What am I doing wrong?
0

#23 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 29 April 2014 - 03:57 AM

Need to see your code and error message. Please post it.
José
0

#24 User is offline   yomer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 01-April 14

Posted 29 April 2014 - 11:19 AM

Sorry, here's the code. Let me know if you need any other code snippet. I really appreciate it.

_form.php:

<?php
/* @var $this TenantController */
/* @var $model TTenant */
/* @var $form CActiveForm */
?>

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
	'id'=>'ttenant-form',
	// Please note: When you enable ajax validation, make sure the corresponding
	// controller action is handling ajax validation correctly.
	// There is a call to performAjaxValidation() commented in generated controller code.
	// See class documentation of CActiveForm for details on this.
	'enableAjaxValidation'=>false,
)); ?>

	<p class="note">Fields with <span class="required">*</span> are required.</p>

	<?php echo $form->errorSummary($model); ?>


	<div class="row">
		<?php echo $form->labelEx($model,'dbu'); ?>
		<?php echo $form->textField($model,'dbu',array('size'=>16,'maxlength'=>16)); ?>
		<?php echo $form->error($model,'dbu'); ?>
	</div>

	<div class="row">
		<?php echo $form->labelEx($model,'e_dbpwd'); ?>
		<?php echo $form->textField($model,'e_dbpwd',array('size'=>60,'maxlength'=>1024)); ?>
		<?php echo $form->error($model,'e_dbpwd'); ?>
	</div>

	<div class="row">
		<?php echo $form->labelEx($model,'business_name'); ?>
		<?php echo $form->textField($model,'business_name',array('size'=>60,'maxlength'=>128)); ?>
		<?php echo $form->error($model,'business_name'); ?>
	</div>

	<div class="row">
		<?php echo $form->labelEx($model,'is_dedicated'); ?>
		<?php echo $form->textField($model,'is_dedicated'); ?>
		<?php echo $form->error($model,'is_dedicated'); ?>
	</div>

	<div class="row">
		<?php echo $form->labelEx($model,'member_type'); ?>
		<?php echo $form->textField($model,'member_type'); ?>
		<?php echo $form->error($model,'member_type'); ?>
	</div>

	<div class="row buttons">
		<?php echo CHtml::submitButton($model->isNewRecord ? 'Create' : 'Save'); ?>
	</div>

<?php $this->endWidget(); ?>

</div><!-- form -->


TTenant.php

<?php

/**
 * This is the model class for table "tenant".
 *
 * The followings are the available columns in table 'tenant':
 * @property string $id
 * @property string $dbu
 * @property string $e_dbpwd
 * @property string $business_name
 * @property integer $is_dedicated
 * @property integer $member_type
 *
 * The followings are the available model relations:
 * @property User[] $user
 */

class TTenant extends CActiveRecord
{
	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'tenant';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('dbu, e_dbpwd, business_name', 'required'),
			array('is_dedicated, member_type', 'numerical', 'integerOnly'=>true),
			//array('id', 'length', 'max'=>10),
			array('dbu', 'length', 'max'=>16, 'safe'),
			array('e_dbpwd', 'length', 'max'=>1024, 'safe'),
			array('business_name', 'length', 'max'=>128),
			// The following rule is used by search().
			// @todo Please remove those attributes that should not be searched.
			array('id, dbu, e_dbpwd, business_name, is_dedicated, member_type', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(			
			'user' => array(self::HAS_MANY, 'TUser', 'tenant_id')
		);
	}
        
        public function beforeSave()
        {
            if ($this->isNewRecord) {
                Common::createMySQLUser($this->dbu,$this->e_dbpwd);
            }
            return parent::beforeSave();
        }
        public function getListOfAllTenants() // used only by app staff to assign users to tenants
        {
            $criteria = new CDbCriteria(array(
                'select'=>'id, business_name',
                'order'=>'business_name ASC',
            ));
            $listOfAllTenants=CHtml::listData($this->findAll($criteria), 'id', 'business_name');
            return $listOfAllTenants;
        }

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'id' => 'Primary key',
			'dbu' => 'Usuario BD',
			'e_dbpwd' => 'Contraseña BD',
			'business_name' => 'Nombre negocio',
			'is_dedicated' => 'BD dedicada',
			'member_type' => 'Tipo miembro',
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 *
	 * Typical usecase:
	 * - Initialize the model fields with values from filter form.
	 * - Execute this method to get CActiveDataProvider instance which will filter
	 * models according to data in model fields.
	 * - Pass data provider to CGridView, CListView or any similar widget.
	 *
	 * @return CActiveDataProvider the data provider that can return the models
	 * based on the search/filter conditions.
	 */
	public function search()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->compare('id',$this->id,true);
		$criteria->compare('dbu',$this->dbu,true);
		$criteria->compare('e_dbpwd',$this->e_dbpwd,true);
		$criteria->compare('business_name',$this->business_name,true);
		$criteria->compare('is_dedicated',$this->is_dedicated);
		$criteria->compare('member_type',$this->member_type);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}

	/**
	 * Returns the static model of the specified AR class.
	 * Please note that you should have this exact method in all your CActiveRecord descendants!
	 * @param string $className active record class name.
	 * @return TTenant the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}
}


create.php

<?php
/* @var $this TenantController */
/* @var $model TTenant */

$this->breadcrumbs=array(
	'Ttenants'=>array('index'),
	'Create',
);

$this->menu=array(
	array('label'=>'List TTenant', 'url'=>array('index')),
	array('label'=>'Manage TTenant', 'url'=>array('admin')),
);
?>

<h1>Create TTenant</h1>

<?php $this->renderPartial('_form', array('model'=>$model)); ?>

0

#25 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 29 April 2014 - 11:35 AM

By default Yii assumes "id" as an autoincremented integer primary key unless you override the primaryKey() function in the model. It appears that you defined "id" as a string. This will not work. In fact for every table where you use a surrogate (instead of a natural) primary key it should be integer, not null and autoincremented. Follow the example in the wiki article.
José
0

#26 User is offline   yomer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 01-April 14

Posted 29 April 2014 - 12:11 PM

View PostJFReyes, on 29 April 2014 - 11:35 AM, said:

By default Yii assumes "id" as an autoincremented integer primary key unless you override the primaryKey() function in the model. It appears that you defined "id" as a string. This will not work. In fact for every table where you use a surrogate (instead of a natural) primary key it should be integer, not null and autoincremented. Follow the example in the wiki article.


I'm actually using a natural primary key on my tenant table(and every other table), and it's set as int, NN and unsigned. I followed every step of the wiki, with the only difference that I added two more columns to the tenant model. Should I just delete any reference to the id on my models?

I'm attaching the actual error I'm getting... and here's my table declaration

CREATE TABLE `tenant` (
`id` int(10) unsigned NOT NULL COMMENT 'Primary key',
`dbu` varchar(16) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Usuario BD',
`e_dbpwd` varbinary(1024) NOT NULL COMMENT 'Contraseña BD',
`business_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Business name',
`is_dedicated` tinyint(1) unsigned DEFAULT '0' COMMENT 'BD dedicada',
`member_type` smallint(5) unsigned DEFAULT '0' COMMENT 'Tipo miembro',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


One more thing I did. I generated both TTenant and VTenant models per the article, and then I created the view, the controller and CRUD using the TTenant as the model class. I didn't catch that the id in the model was generated as a string until you mentioned it. The weird thing is that the error when trying to create a new tenant. Can't I just change the property to int, or do I have to try and generate the model code again with Gii's CRUD Generator?

Attached File(s)


0

#27 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 29 April 2014 - 03:59 PM

If you're using MySQL you're missing the AUTO_INCREMENT directive for the "id" column. As for the error message, it's related to "dbu":

 array('dbu', 'length', 'max'=>16, 'safe'),


You cannot mix validators in the same statement so 'length' and 'safe' shouldn't be combined. I didn't catch it initially but the error message makes it very clear:

C:\wamp\yii-1-1-14\framework\base\CModel.php(287): CValidator::createValidator("length", TTenant, "dbu", array("max" => 16, 0 => "safe"))


Fix those and you should be alright.
José
0

#28 User is offline   yomer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 01-April 14

Posted 29 April 2014 - 06:55 PM

That did it! I had to remove all key relationships with the tables before modifying the id to be AI, and reassign them again(is there a way to do it without removing foreign keys?). I changed the safe validator to its own element. And I had forgotten to remove the user and pass fields from the tenant _form.

Thank you very much!
0

#29 User is offline   JFReyes 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 508
  • Joined: 28-October 09
  • Location:Puerto Rico

Posted 29 April 2014 - 07:21 PM

I'm glad you could resolve the issues. If I may suggest, I recently "discovered" (because they've been around for a while) database migrations. Essentially you define your DB tables in PHP code instead of using the DB manager and create/drop/redefine them in one easy step, the Yii way. You can create indexes & foreign keys and even do an initial population of records. I'm certainly not going back to MySQL Workbench/SQL Server Mgmt Studio unless the job requires some esoteric tweaking beyond Yii's capabilities.

I don't mean to be prickly but perhaps you have confused a "natural" from a "surrogate" key. My understanding is that a natural key is akin to a social security number, while a surrogate key (the preferred way according to the experts) has nothing to do with nature; it's strictly for maintaining referential integrity.

Enjoy! :)
José
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users