Hi I’m trying to use a model that will generate dynamic table name from another database. I’ve managed to set the table name by overriding the tableName() function. But i’m getting an error saying
The table "powerDovakin_{FUS.THUM}" for active record class "PowersTransactions" cannot be found in the database.
.
Here is the model class in question
<?php
class PowersTransactions
extends CActiveRecord {
public $symbol ;
public function __construct ($symbol) {
$this->symbol = $symbol;
}
/**
* @return string the associated database table name
*/
public function tableName () {
return "powerDovakin_{" . $this->symbol ."}";
}
/**
* @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 (
) ;
}
/**
* 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 InsidersTransactions the static model class
*/
public static function model ( $className = __CLASS__ ) {
return parent::model ( $className ) ;
}
/**
* Overriding parent getDbConnection to allow for use of different database
*/
public function getDbConnection () {
return Yii::app ()->powersDovakin ;
}
}
Now i’ve turned on logging and the trace shows that the error is being thrown when this query is being executed… Here are some of the relevant lines from the stack trace
12:19:45.053172 trace system.db.CDbConnection
[ocak07jk4q3v8nfd535io8fdd4] Opening DB connection
in /var/www/html/PowerAnalysis/protected/models/PowersTransactions.php
(283)
in /var/www/html/PowerAnalysis/protected/models/PowersTransactions.php
(191)
in /var/www/html/PowerAnalysis/protected/views/realTime/_powerView.php
(9)
12:19:45.053564 trace system.db.CDbCommand
[ocak07jk4q3v8nfd535io8fdd4] Querying SQL: SHOW FULL COLUMNS FROM
`powerDovakin_{FUS.THUM}`
in /var/www/html/PowerAnalysis/protected/models/PowersTransactions.php
(191)
in /var/www/html/PowerAnalysis/protected/views/realTime/_powerView.php
(9)
in /var/www/html/PowerAnalysis/protected/views/realTime/view.php (715)
12:19:45.053858 error system.db.CDbCommand
[ocak07jk4q3v8nfd535io8fdd4] CDbCommand::fetchAll() failed:
SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command
denied to user 'user1'@'localhost' for table 'THUM}'. The SQL statement
executed was: SHOW FULL COLUMNS FROM `powerDovakin_{FUS`.`THUM}`.
in /var/www/html/PowerAnalysis/protected/models/PowersTransactions.php
(191)
in /var/www/html/PowerAnalysis/protected/views/realTime/_powerView.php
(9)
in /var/www/html/PowerAnalysis/protected/views/realTime/view.php (715)
From the above trace what i could find out is that Yii is putting backticks (`) around the dots and maybe interpreting the portion after the dots as a column name.
My question is how can i make Yii use this sort of table names. I wish i could change the table names but my hands are tied at this moment. I just can’t change them as they are not mine. So again the table names are like
Is it possible to make the model accept such names. Please provide any sort of help as i can’t find any solution to this problem. I would really appreciate any help i can get on this.
MariaDB [test]> create table powerDovakin_{FUS.THUM} (id integer);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '{FUS.THUM} (id integer)' at line 1
Those are not valid table names. The quoting Yii puts around them is correct.
I’m sorry to say these are valid because there are at present several thousands such tables in the database which have such name. And i’m cursing the developer who did such atrocity. But like i said in my post i just can’t change them now. All i’m allowed to do is to run Select, Insert, or Update on them.
In mysql on in traditional PDO you have to specify the table name as powerDovakin_{FUS.THUM}… You have to surround them with backticks… Try it now it will work
create table `powerDovakin_{FUS.THUM}`
Anyways thanks for your reply, but isn’t there any way to incorporate such names in my model.
Oh sorry, you’re right. The dot in the table name causes the problem. Try prepending the database to it. If that doesn’t help, your only solution is to either extend base classes for db operations or create views in the database with more normalized names.
Hi , Thanks for the solution. But it didn’t worked. I’m getting the same error. So i guess my only solution is to extend the base class. Can you please provide any sort of help on how to do it. And i also can’t create views because the system has very real time constraint on it.
I was wondering what if i could write manual queries inside the model class. So for example here is the search() function inside the above model class.
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 ) ;
$criteria->compare ( 'symbol' ,
$this->symbol ,
true ) ;
$criteria->compare ( 'thumType' ,
$this->thumType ,
true ) ;
$criteria->compare ( 'timestamp' ,
$this->timestamp ,
true ) ;
$criteria->compare ( 'rune' ,
$this->rune ,
true ) ;
$criteria->compare ( 'date' ,
$this->date ,
true ) ;
$criteria->compare ( 'destruction' ,
$this->destruction ,
true ) ;
return new CActiveDataProvider ( $this ,
array (
'criteria' => $criteria ,
) ) ;
}
Can i replace this with manual query (like "SELECT * FROM tablename WHERE …" ), so that the parts of the program which depends on the search function will continue to work. There are many gridviews in the application which depends on this and many similar search functions.
WOW! It Worked! … Wow i can’t believe my eyes it really worked ! How’d you do it ? I can’t thank you enough of how much this have helped me. I was literally about to give up any hope after trying for several days now. But whatever magic you did it worked flawlessly. Thanks again…
Adding simple views that only perform one SELECT query does not introduce any performance penalties at all. But that’s just a side note, as you already found the solution.
Yes i understand. But in this case each such tables get around 1000 queries per second. I mean its already a bit slow so i didn’t wanted to incur any more overhead.
this automatic rules from table schema, and check in field is allow null or not
/**
* @return array validation rules for model attributes.
*/
public function rules()
{
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
$columns = Yii::app()->db->schema->tables[$this->_tableName]->columns;
foreach ($columns as $column => $schema) {
if (!$schema->allowNull) {
$tmp_columns[] = $column;
}
}
$col = implode(',',$tmp_columns);
//var_dump($columns);
return array(
array($col,'required')
);
}
