5

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?

dgilperez
  • 10,716
  • 8
  • 68
  • 96
sparkyspider
  • 13,195
  • 10
  • 89
  • 133

1 Answers1

5

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.

Workbench Data Insert

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.

Tick Insert option

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!

YoussefDir
  • 287
  • 1
  • 3
  • 16
Namphibian
  • 12,046
  • 7
  • 46
  • 76
  • Thanks so much for your help. The stored procedure generates 30 000 records that need to be inserted into the database. The other one populates countries and related cities. The datasets are too large to insert manually. Any further solutions still welcome. – sparkyspider May 08 '12 at 10:47
  • @Mark Van Wyk these stored procedures you mentiod do they get created during the forward engineering? – Namphibian May 08 '12 at 11:53
  • Namphibian, yes, I've added them as "routines", and they automatically create the procedures during forward engineering. – sparkyspider May 08 '12 at 13:37
  • 1
    Mark during the review part of the script just before you run it you can edit the script. You can then insert the calls to the stored procedures manually at the end of the script to execute them after table creation. I tested this and it works. Just save the script as well so you can rerun it later. – Namphibian May 08 '12 at 13:53
  • I'm looking for an automated solution, with the idea being that other developers can simply forward engineer the script and forget about it. I however, will put it in the instructions for other developers to add the routine calls manually to the SQL script before it's run. – sparkyspider May 09 '12 at 07:08
  • Thanks so much your your efforts, you'll be awarded 1/2 the bounty for being the highest scoring answer. – sparkyspider May 09 '12 at 07:14