With MySQL views of course!

A view is a table created on the fly from a sql statement.

The idea is to replace our target application users table by a view, mirroring your users table with this app attended fields.

Here the Bugzilla 3.0 profiles table schema:

Oh and let’s remember than login is e-mail address!

Now, we have 2 type of fields:

1. those already in our own users table, like the login (the e-mail actually for Bugzilla), the realname or the password
2. the new fields

First, we add these news fields in our own users table:

ALTER TABLE utilisateurs ADD `bugzilla_disabledtext` mediumtext; ... ALTER TABLE utilisateurs ADD `bugzilla_disable_mail` tinyint(4) NOT NULL DEFAULT '0',

Note than we prepend the fields to avoid future conflicts with other apps.

Then, rename (delete isn’t a good idea if you’ve to rebuild your view) the target app users table:

ALTER TABLE profiles RENAME profiles_dist

Now, we can create the view:

CREATE ALGORITHM=UNDEFINED VIEW profiles AS select `espacewin`.`utilisateurs`.`user_id` AS `userid`,`espacewin`.`utilisateurs`.`email` AS `login_name`,`espacewin`.`utilisateurs`.`user_password` AS `cryptpassword`,`espacewin`.`utilisateurs`.`bugzilla_disabledtext` AS `disabledtext`,`espacewin`.`utilisateurs`.`bugzilla_mybugslink` AS `mybugslink`,`espacewin`.`utilisateurs`.`bugzilla_extern_id` AS `extern_id`,`espacewin`.`utilisateurs`.`bugzilla_disable_mail` AS `disable_mail`,`espacewin`.`utilisateurs`.`username` AS `realname` from `espacewin`.`utilisateurs`

As you can see, it’s mainly a SQL query.

If you use another db, with another login/pass, specify the user authorized to access the db where your users table is:

CREATE ALGORITHM=UNDEFINED DEFINER='Zebra'@'localhost' VIEW profiles AS select `espacewin`.`utilisateurs`.`user_id` AS `userid`, ...

Congratulations, you’ve finished.

Quick question: why add fields to our users table instead keep app one to this use?

Well … your app has to perform some INSERT or UPDATE query and this is only possible on views created with a simple one table query.

Oh, and I’ve said “no code”.

You can use Navicat to design the view instead handwriting it:

I use this method on Espace Win to integrate Pluton with Bugzilla, phpBB and flyspray.