{"id":30,"date":"2007-07-08T05:48:27","date_gmt":"2007-07-08T05:48:27","guid":{"rendered":"http:\/\/www.dereckson.be\/blog\/2007\/07\/08\/howto-use-your-login-pass-system-to-bugzilla-or-other-app-without-edit-any-line-of-code\/"},"modified":"2020-10-10T04:51:31","modified_gmt":"2020-10-10T02:51:31","slug":"howto-use-your-login-pass-system-to-bugzilla-or-other-app-without-edit-any-line-of-code","status":"publish","type":"post","link":"https:\/\/www.dereckson.be\/blog\/2007\/07\/08\/howto-use-your-login-pass-system-to-bugzilla-or-other-app-without-edit-any-line-of-code\/","title":{"rendered":"Howto use your login\/pass system to Bugzilla (or other app) without edit any line of code ?"},"content":{"rendered":"\n

With MySQL views of course!<\/p>\n\n\n\n

A view is a table created on the fly from a sql statement.<\/p>\n\n\n\n\n\n\n\n

The idea is to replace our target application users table by a view, mirroring your users table with this app attended fields.<\/p>\n\n\n\n

Here the Bugzilla 3.0 profiles table schema:<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Oh and let’s remember than login is e-mail address!<\/p>\n\n\n\n

Now, we have 2 type of fields:<\/p>\n\n\n\n

  1. those already in our own users table, like the login (the e-mail actually for Bugzilla), the realname or the password<\/li>
  2. the new fields<\/li><\/ol>\n\n\n\n

    First, we add these news fields in our own users table:<\/p>\n\n\n\n

    ALTER TABLE utilisateurs ADD `bugzilla_disabledtext` mediumtext;<\/code> ...<\/code> ALTER TABLE utilisateurs ADD `bugzilla_disable_mail` tinyint(4) NOT NULL DEFAULT '0',<\/code><\/p>\n\n\n\n

    Note than we prepend the fields to avoid future conflicts with other apps.<\/p>\n\n\n\n

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

    ALTER TABLE profiles RENAME profiles_dist<\/code><\/p>\n\n\n\n

    Now, we can create the view<\/a>:<\/p>\n\n\n\n

    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`<\/code><\/p>\n\n\n\n

    As you can see, it’s mainly a SQL query.<\/p>\n\n\n\n

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

    CREATE ALGORITHM=UNDEFINED DEFINER='Zebra'@'localhost' VIEW profiles AS select `espacewin`.`utilisateurs`.`user_id` AS `userid`, ...<\/code><\/p>\n\n\n\n

    Congratulations, you’ve finished.<\/p>\n\n\n\n

    Quick question: why add fields to our users table instead keep app one to this use?<\/p>\n\n\n\n

    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.<\/p>\n\n\n\n

    Oh, and I’ve said “no code”.<\/p>\n\n\n\n

    You can use Navicat<\/a> to design the view instead handwriting it:<\/p>\n\n\n\n

    \"\"<\/a><\/figure>\n\n\n\n

    I use this method on Espace Win to integrate Pluton with Bugzilla, phpBB and flyspray.<\/p>\n","protected":false},"excerpt":{"rendered":"

    With MySQL views of course!<\/p>\n

    A view is a table created on the fly from a sql statement.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[11],"class_list":["post-30","post","type-post","status-publish","format-standard","hentry","category-misc","tag-general"],"_links":{"self":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":1,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"predecessor-version":[{"id":785,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions\/785"}],"wp:attachment":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}