Hải Phan Thanh Hải Phan Thanh - 7 months ago 22
SQL Question

How to synchronize database in INSERT INTO query

Here is my query.

INSERT INTO compare_moodle
(userid,
username,
firstname,
lastname,
courseid,
coursename)
SELECT mdl_user.id,
mdl_user.username,
mdl_user.firstname,
mdl_user.lastname,
mdl_course.id,
mdl_course.fullname
FROM mdl_user
JOIN mdl_role_assignments ra
ON mdl_user.id = ra.userid
JOIN mdl_role r
ON ra.roleid = r.id
AND r.id = 5
JOIN mdl_context c
ON ra.contextid = c.id
JOIN mdl_course
ON mdl_course.id = c.instanceid


When
mdl_user
or another database change (deleted / updated / inserted) how
compare_moodle
can be synchronized? I think use trigger but don't know how.

Answer

It's possible to use a VIEW in place of a table for the purpose you present. You'd do that like this:

CREATE OR REPLACE VIEW compare_moodle AS
 SELECT mdl_user.id AS userid, 
        mdl_user.username, 
        mdl_user.firstname, 
        mdl_user.lastname, 
        mdl_course.id AS courseid, 
        mdl_course.fullname AS coursename 
   FROM mdl_user 
   JOIN mdl_role_assignments ra    ON mdl_user.id = ra.userid 
   JOIN mdl_role r                 ON ra.roleid = r.id 
                                  AND r.id = 5 
   JOIN mdl_context c              ON ra.contextid = c.id 
   JOIN mdl_course                 ON mdl_course.id = c.instanceid;

This gives you a virtual table that is always transactionally up to date with your source tables. SQL best practice teaches us that this approach, without data redundancy, is the best way to do what you want.

The problem, of course, is performance. If you use this compare_moodle view a lot you may have slowdowns. Correct indexing of your tables can practically eliminate those slowdowns. I suggest you get your application working with a view, and then figure out whether you have a performance problem.

What you asked for in your question is a so-called materialized view -- a view represented by an actual table. In some makes and models of DBMS you can give a command like CREATE OR REPLACE MATERIALIZED VIEW ... and the DBMS does all the work. But, MySQL doesn't offer that.

There are various MySQLish approaches to managing materialized views. See here for more information. http://dba.stackexchange.com/questions/86790/best-way-to-create-a-materialized-view-in-mysql But this approach is a big hassle, and a maintenance headache compared to using an ordinary view.

Comments