MySQL Workbench is a unified visual tool for database architects and developers. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more, so in this tutorial we are going to make Android SQLite database using Workbench.
Using MySQL Workbench you can export SQL Code but for Android you need to export SQLite code. So to design Android SQLite database using Workbench we need to download it from here download MySQL workbench and let’s get started.
Android Design SQLite Database using Workbench
1. After downloading MySQL Workbench open it and lets add a new model by clicking File => New Model
In my case we will add just two simple tables, one for Employee which has some attributes like: Name, Age, Mail, Phone and the other for Department which has attributes like: Title.
The Employee table has multi-valued attribute which is Phone, so we create a table called EmployeePhone and link between by putting a reference for EmployeeID in EmployePhone table.
Click on Add Table, call it Employee and add some attributes as shown in the following pic, then add Department table with title attribute.
Add another table call it EmployeePhone, and add attribute of PhoneNumber.
2. Click on Add Diagram, it will open an empty panel, just drag and drop your tables from the left into this panel.
Now we need to link between these three tables, Employee and EmplyeePhone table has 1:M relationship so we can make this relation by selecting 1:M relationship from the left side as shown below. -Start by clicking on the EmplyeePhone table then Employee table to create this relationship-
Do this step for Employee and Department so the output will look like that:
3. Now we can easily export this database design into MySQL code, by using File => Export and follow the steps, but to make Android Design SQLite Database using Workbench, , but we need to export it to SQLite so that we can use it in Android App.
4. So we come to the magical part, there’s a plugin called ExportSQLite which is used to export SQLite files from the MySQLWorkbench software.
The generated SQLite file can be used directly into your iOS or Android project.
How to use:
- Download the plugin from here.
- From MySQL Workbench, go to “Scripting -> Install Plugin/Module…”.
- From the dialog box, select the ExportSQLite.grt.lua script.
- Restart MySQL Workbench.
- To generate your SQLite file, open your model and go to “Plugins -> Utilities -> Export SQLite CREATE script”.
- Type a file name and hit save.
- Enjoy your SQLite file.