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.