How do I automatically execute a MySQL script or routine after forward engineering from model

Question

I've built a EER Model in MySQL Workbench that I forward engineer to create the database. The forward engineering works perfectly, and the database is created from the diagram as expected.

Apart from tables, there are also some Stored Procedures (aka Routines) that I've included in the model. These routines are designed to only be run once, as soon as the database has been set up. They automatically insert necessary data into the tables.

My question is, how can I get the forward engineering process to automatically call/execute one of these routines once the tables have been created.

At the moment, I have to forward engineer the database, and then manually call the stored procedures?

Answer 1

In your EER diagram on the workbench right click on a table and select edit table. This will open a pane at the bottom with a couple of tabs. The tabs are table, columns, indexes, foreign key, etc. There is a tab called insert. This tab allows you to insert records into the Model database.

When you click on the insert tab it will show a grid. Add the records you want to insert onto this grid. Make sure you commit these records. See screenshot for example.

Now when you forward engineer the database on the very first screen there is an option to Generate insert statements. Tick the option forward engineer and the data you want inserted will be scripted when you create the model. Save the script so you can run it over and over without going into the MySQL workbench.

I have not found options to update, delete or do other data manipulation in the workbench but I think this is what you are looking for.

NOTE : To directly import the records, you won't be able to do that via the workbench; there is no option. You can save the records to file. However to import/create them you would need to add them one at a time (from the modeller). You could however make a backup of the MySQL database with the records in already. Then copy those INSERT Statements from the MYSQL backup script into your setup script.

Steps would be:

1. Create database.

2. Import the files with the setup/config records into newly created database

3. Backup database

4. Open backup file, then copy and paste the INSERT statements you are looking for into the setup script created by the MySQL Workbench

UPDATE:

I did some experimenting when you get to the review script to be executed step in the forward engineering you can also at the end call the stored procedures (as you mentioned) by editing the script. Once done save the script to file and test.

Hope that helps!