Is it possible to update views columns automatically (Mysql)?

Question

I have the following scenario:

There is one database (main) that holds common information such as 'modules', 'menu', etc and one database for each project containing a view for each of those tables in main database. For example:

main

• modules (id, description)
• menu (id, label, href, ...)
• ...

project1

• modules_view
• menu_view

Those views where created as simple as:

CREATE VIEW menu_view AS SELECT * FROM main.menu

Now, I have to create an 'order' column for the menu but the view columns aren't being updated.

ALTER TABLE `menu` ADD `menu_order` INT NOT NULL AFTER `href`;

Is it possible to maintain the views columns updated without having to do it mannualy each time I have to create a new column in the main table?

---

OBS: there are 10+ projects... So it will become harder to maintain as this number grows

Answer 1

You can have one text/sql file with the definition of all your views and run it everytime you update your tables. So, you don't need to update every view.

Something like that:

DROP VIEW IF EXISTS menu_view;
CREATE VIEW menu_view AS
    SELECT * FROM main.menu;

DROP VIEW IF EXISTS table2_view;
CREATE VIEW table2_view AS
    SELECT * FROM main.table2;