How to patch DB_active_rec to support dbprefix

Posted: 26 April 2007 10:22 PM

[ Ignore ]

Summer Student

Total Posts: 23

Joined 04-23-2007

I wanted $this->db->select() to support the dbprefix. The standard CI base does not support this. I made the following modification to enable it.

function select($select = '*')
{
   if (is_string($select))
   {
      $select = explode(',', $select);
   }
   foreach ($select as $val)
   {
      $val = trim($val);
      if ($val != '')
      {
         /* Begin RGT 25 Apr 2007 */
         // Replace any word.word with PREFIXword.word
         if ($this->dbprefix)
         {
            $val = preg_replace('|([\w\.]+\.[\w\.]+)|', $this->dbprefix . "$1", $val);
         }
         /* End RGT 25 Apr 2007 */
         $this->ar_select[] = $val;
      }
   }
   return $this;
} 

With this I can now write the following:
Assuming that dbprefix = ‘a_’ in config/database.php…


$this->db->select('cust.cust_id, cust.cust_name');

produces the following SQL:

SELECT a_cust.cust_id, a_cust.cust_name .....

Profile

champs

Posted: 27 April 2007 12:12 AM

[ Ignore ] [ # 1 ]

Lab Assistant

Total Posts: 187

Joined 06-25-2006

... too bad it will also ‘SELECT a_alias’, instead of ‘SELECT alias’. I’ve been down that road, it went into a recent version of CI, and it broke properly-written code. Consider:

// get every student's first & last names from the roster, along with their parent's name
$this->db->select('student.firstname AS student_fname, student.lastname AS student_lname, parent.firstname AS parent_fname, parent.lastname AS parent_lname');
$this->db->from('roster AS r');
$this->db->join('person AS student', 'r.studentid=student.personid');
$this->db->join('person AS parent',  'student.parentid=parent.personid');
... 

If you have to deal with table prefixes, just use aliases for ALL table references. It takes almost no effort.

Signature

Join us in #codeigniter on irc.freenode.net

Profile

champs

Posted: 27 April 2007 12:17 AM

[ Ignore ] [ # 2 ]

Lab Assistant

Total Posts: 187

Joined 06-25-2006

That, or I could have just dredged up the old thread about it.

Signature

Join us in #codeigniter on irc.freenode.net

Profile

ryantxr

Posted: 27 April 2007 12:52 AM

[ Ignore ] [ # 3 ]

Summer Student

Total Posts: 23

Joined 04-23-2007

The point of the prefix is to allow multiple instances of the same application to share a database. One instance would use tables a_xxx the other b_xxx etc. Aliases do not solve this problem.

Also, tables with names like ‘order’ can be a pain in SQL. Putting a prefix solves that.

I have removed all use of active record from my application. I now use :


$this->db->query($sql, array($arg1, $arg2,...));

At this point I define the table names in the constructor as class variables. I’m open to suggestions on a better place to make these definitions as it isn’t good to have to do this in every constructor.

$this->customer_table = $this->db->dbprefix . "customer";
.
.
.
/** Use class variable to build SQL **/
   $sql = "SELECT c.name, c.id FROM $this->customer_table c ".
          "WHERE c.name LIKE 'a%'"; 

Profile

champs

Posted: 27 April 2007 07:37 AM

[ Ignore ] [ # 4 ]

Lab Assistant

Total Posts: 187

Joined 06-25-2006

Aliases absolutely do solve this problem, and you’ll see that if you just follow the link I provided.

The methods from() and join() can safely assume that the first/only parameter passed to them refers to an actual table name, so they apply the table prefix to the beginning of the string that is passed to them. If you alias the table names in those statements, then you don’t have to think about what the prefix is, and select() does not have to be clever about figuring out whether ‘foo.bar’ refers to an alias or an actual table named ‘foo’.

Signature

Join us in #codeigniter on irc.freenode.net

Profile

‹‹ Images in CodeIgniter htaccess question ››

Username		Remember Me?
Password		forgot password?

Post Marker Legend
	New posts	Hot Topic with new posts	New Poll	Moved Topic	Sticky topic
	No new posts	Hot Topic with no new posts	Old Poll	Closed Topic	Announcements

Visitor Statistics
The most visitors ever was 819, on March 11, 2010 11:15 AM
Total Registered Members:	120562	Total Logged-in Users:	24
Total Topics:	126609	Total Anonymous Users:	1
Total Replies:	665585	Total Guests:	333
Total Posts:	792194